Add Space to Field that is consistent format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column that is fixed length formatted as such

N4425501C405
N4425503D113
N4425502M345
et

I want to insert a space between N44255 and 01, between 01 and C, between C and 4050 with the result of a fiel
N44255 01 C 405

I don't mind looking up the formulas, but is TRIM what I should be looking at. Can you give a suggestion

My problem, we have two database, neither of which formatted the contract number the same way, so I have to dump one database to one spreadsheet, dump the other database to another spreadsheet and then lookup to share info between the two. The lookup's don't work if the key isn't formatted the same:

Thank you. And if I don't thank you in person I am thinking it. I am just learning how to use these Q&A rooms and sometimes I type in a question and can't find it again. This time I am saving the subject and Newsgroup info so I can get back to it:) Thank you for all of your patience. Esp Frank Kabel and Peo Sjoblom who have helped in past.
 
Hi,

This will do the trick.

=LEFT(A1,6)&" "&MID(A1,7,2)&" "&MID(A1,9,1)&" "&RIGHT(A1,4)


Trim removes unwanted spaces.


Gre
 
Back
Top