Format question for excel

[bcblair]

I have some fields that I imported from a Oracle database.

some are like this:gdot1901
some are like this: 3000314
I need to remove any leading spaces and to add a space after the fourth character after the spaces are removed

For gdot1901 I use =LEFT(K2,4)&" "&MID(K2,5,6) TO RETURN gdot 1901

For 3000314 I have to use =TRIM(K2) TO REMOVE THE BLANKS THEN =LEFT(K2,4)&" "&MID(K2,5,6)TO GET 3000 314

How can I combine the two formulas into one I thought =TRIM(K2)&=LEFT(K2,4)&" "&MID(K2,5,6). But it doesn't work.

I can do the trim formula on all and removes the leading spaces and doesn't hurt the one without leading spaces but then I do the next formula. Can I do it with one formula?

 

[mdav2]

Try using the following syntax:

=LEFT(TRIM(K2),4)&" "&MID(TRIM(K2),5,6)

This trims the string then applies the formatting and would work in the both instances. I tried this using the example provided and it works.

Mark.