Removing an extra space within a string

[Bostbrother]

I am importing raw data from an on line game I play; I import a string format that I need to convert to a number (currency).


for example: 203 101 is the string that gets imported

I tried doing the find " " and replace with "" first but that does not work. I have been clicking inside the text and removing the space with a backspace and then Excel immediately converts to a number.


I am sure there is an easier way to do this but need some help. Thank you

 

[Hui]

What should it read?

Have you used a Trim() on the cell with the text ?

 

[Bostbrother]

Here is the data I am trying to clean.


Income Expense

Staff Salary 0 83 869

Money Transfer 350 000 0

Repair work 0 21 361

Electricity 0 11 360

Water 0 4 544


I need to convert 350 000 to $350,000, 83 869 to $83,869, etc


If I select the entire range and replace " " with "" it will make "Staff Salary" "StaffSalary", "Money Transfer" "MoneyTransfer", etc but does nothing to the

number strings that I want to convert to number/currency.


As soon as I mouse into the cell and remove the space manually with a back space, as soon as I hit enter, Excel immediately recognizes the contents of the cell as a number and then I can format as currency.


Also, Excel immediately recognizes the zeros as numbers.


The Trim function has no effect on the cell contents whatsoever.

 

[Hui]

Is "Money Transfer 350 000 0" in 1 cell or 5 cells?

Are you sure 350 000 0 is 350,000 not 3,500,000?