Excel 2007 : Removing extra space from cell content between words

**trillkilo** · 02-26-2011, 02:35 PM

I have an excel file (2007) which contains project number code and project name values in the same cell. Spacing between the numerical code and start of project name is inconsistent. In some cases there is one spece and in other cases there are 2 spaces between. Samaple Cell content with one space "23445 Horse Project". Sample cell content with 2 spaces "45670 Computer Science". all numerical codes have the same number of digits, 5. I simply want to be able to create 2 versions of the file. One with one space between numerical code and start of project name in each cell. And another with 2 spaces in between....

Please advise?

Currently I'm doing this via a method which I think is much longer than it needs to be. I'd like a formula that I can apply to an entire column to do this in bulk. I'm currently doing a combination of text to column, then concatenating. There must be a quicker way.

**Marcol** · 02-26-2011, 03:12 PM

Try this
For one space

=TRIM(A2)

For two spaces

=SUBSTITUTE(TRIM(A2)," ",REPT(" ",2),1)

If you omit the last arguement from the above i.e. ,1 then all spaces will be replaced with double spaces, change it to ,2 and the second space will be doubled...etc

Trim as a worksheet function removes all extra spaces in a string, unlike the VBa Trim which only removes leading and trailing spaces.

Hope this helps

**L.J** · 02-26-2011, 03:16 PM

Okej,
I presume the project code and numbers are in the same column (let us in this case assume they are in column A). You can use the TRIM formula to remove all but one space between words.

example: in cell B1. =TRIM(A1)
Then populate the forumal for the entire range.

To get the project name you can use MID in the next column
=MID(B1,7,50)
That will extract the project name (up to 50 charaters)

To get the version with dubble space between do like this. in the last column.
=C1&" "&D1

I hope this helps.

Then in the next column, use the LEFT formula like this.
=LEFT(B1,5)
This will extract the 5 digits