Extract extra spaces at the end of text in a cell

[reneev]

So, our payroll system doesn't know how to properly space text in a report to be exactly how long the word or words are therefore we have to estimate the spacing to be x number of spaces. Which means if the word or words are shorter than x number, the rest of the field contains spaces.

For example, if we set the the full name field to 20 characters then the following name would come out like this (where ^ are extra spaces).

Doe, John F^^^^^^^^^ (11 actual characters and 9 spaces at the end).

This makes it difficult to use the data elsewhere without manually deleting the extra spaces or using the Text to Columns function then Concatenating everything back together.

This happens for all fields in our reports however it's only a real problem with text because number fields usually have a set number of characters (dates, SS#, etc...)

Is there a better way to strip those extra spaces?

 

[Scott T]

you can use trim to remove spaces except the ones between words

=TRIM("text to trim   ")

 

[jtakw]

Hi,

This is what Scott means:

Book1
	A	B	C	D	E	F	G
1	Doe, John F		20		Doe, John F		11
Sheet16

Cell Formulas
Range	Formula
C1	=LEN(A1)
E1	=TRIM(A1)
G1	=LEN(E1)

I'd imagine you'd need to insert a column next to the data to be trimmed, then use the new column's data for your other calculations; however, if you can't do that, then you'll need a VBA solution.

 

[reneev]

OMG, thank you so much!!!!! That worked perfectly!

 

[jtakw]

You're welcome.