Mobile phones are truncated and 0 is missing VBA

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Dear Sir or Madam

1637149764856.png


For using a macro I download a data report from the website's platform, and one of the donor’s mobile phones has an error, the numbers are truncated. Please see my screenshot. How do I resolve this please?

Also, the ‘0’ digit are missing from the start of the mobile numbers as well. What is the best way of doing this?

For the above, I am using a macro file to download a report.

Hope this makes sense.

Best Regards

vbanewbie68
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Thanks for that. I am looking at the link so where about can I add the exact code onto my vba? Will it be on the Sub Main ()?
The section that shows the exact code is this:

1637165665748.png


So the first file name is the current file name (with the ".CSV" extension), and the second file name is the new one you want (with the ".TXT extension).

You will need to put line in your code before you open the data file, and make sure that you are then opening the "TXT" one.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have managed to sort out the issue. The mobile phones look normal. Basically when I download a csv report. Instead of open the file. I click Save as to the desired folder. Remember not to open the file. Let the macro do the job to open it instead. The result for that has passed with showing normal mobile numbers! :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Ah, where you opening the file before and saving it?
Saving it would unfortunately save Excel's automated conversions, making those permament.

I usually recommend that people NEVER open CSV files in Excel if you want to retain the format.
They are best viewed in Text Editors like NotePad.
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Yes that is right I did open the csv file. Thank you sir and all your kind help on this matter which is greatly appreciated!
I will make sure that I will never open CSV files if I want to retain the format.:):)
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Morning sir
Back to this how to take a view to have a look at the data without opening CSV file? As you said Text Editors like NotePad. How does it work that way please?
Regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Back to this how to take a view to have a look at the data without opening CSV file? As you said Text Editors like NotePad. How does it work that way please?
All computer that have Microsoft Office also have NotePad (and WordPad).
If you browse to the file using File Explorer, right-click on the file and select "Open With" and select "NotePad".

When you use a Text Editor to view a CSV file, you will see the data in the file EXACTLY as it exists, without any changes or conversions (like Excel like to do to it).
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top