Removing all Spaces

[PvtSmity]

Hi All,
I have 1 column with a series of numbers that has 6 digits then 2 spaces and then 4 to 5 digits. i am trying to remove both spaces. I am able to remove 1 space with TRIM but I need to remove the 2nd space. My co-worker has a concatenate that has no spaces and can't add spaces to their concatenate cause it is looking at info across our network. Is there a formula that can remove both spaces or should a macro be written to remove them?

248141 24000
248141 5000

 

[Rick Rothstein]

Hi All,
I have 1 column with a series of numbers that has 6 digits then 2 spaces and then 4 to 5 digits. i am trying to remove both spaces. I am able to remove 1 space with TRIM but I need to remove the 2nd space. My co-worker has a concatenate that has no spaces and can't add spaces to their concatenate cause it is looking at info across our network. Is there a formula that can remove both spaces or should a macro be written to remove them?

248141 24000
248141 5000

If you are doing this by formula, use the SUBSTITUTE function to change " " to "". Note that if you use the suggested SUBSTITUTE function, you will no longer need to use TRIM.

 

[Joe4]

The easiest way it to just use Find/Replace replacing one single space with nothing. That will remove all spaces for all records at once.
You can make this VBA code, if you want, by turning on the Macro Recorder and doing this process manually.

 

[PvtSmity]

Thanks Rick and Joe4. Its perfect