Clean up string.

[g8orade]

Hi all and thanks in advance,
I have this report that goes into excel where I imported data and would like to clean up the description string.
Some of these came across with extra characters in them and others are ok. The data that I am trying to clean up looks like this: "0 LABOR POOL ONHAND" the first character is always a number and is preceded by a space. thanks for any help.
G8orade.

 

[KenWright]

Select data, do Data / Text To Columns / Fixed width, set a break after the space and hit Next, click on first column and choose 'Do not import column' and hit finish.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[g8orade]

Thanks Ken for quick response, normally that would work but some of the imported data does not have the number and space in them for example, on some I may have "0 LABOR POOL ONHAND" and on others it may be "LABOR POOL ONHAND" so if I do as suggested I will loose some of the characters on the ones that are ok now. Sorry about not being clear on this.
G8orade.
Thanks again for your help.

 

[Lilliabeth]

=IF(MID(A1,2,1)=" ",RIGHT(A1,LEN(A1)-2),A1)

 

[g8orade]

That worked great Lillabeth. Thanks so much. As always the tek-tips forums are the bestest.
G8orade

 

[KenWright]

Apologies from me, I should have read the damn question properly first.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Hi g8orade,

Lilliabeth's formula still leaves a leading space, since your string has two spaces after the number. To delete both of those spaces the formula should be:
=IF(MID(A1,2,1)=" ",RIGHT(A1,LEN(A1)-3),A1)
Even so, it still only delets the number if the second character in the string is a space. It won't work if the second character is a number, as you'd get with "01 LABOR POOL ONHAND" or " 0 LABOR POOL ONHAND", for example.

The following formula deletes both leading and trailing spaces, and caters for leading numbers of any length:
=TRIM(IF(ISNUMBER(LEFT(TRIM(A1))^1),RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))),A1))

Cheers