INSERT A SPACE INTO A TEXT VALUE

[JonRowland]

Hi,

Hope someone can help me.

I have a number of text values. However, I need to be able to add a space into these after the fifth character.

Any ideas how I can do this?

Thanks

Jon

 

[excelpete1]

digging up this good old thread

I'm grabbing lists of names and combining the first name and last name columns into one. this leaves me with names that look like this: TomSmith

how do I add a space between first and last name? all last names start with a capital letter.

i'm trying to have it look like this: Tom Smith

 

[RoryA]

Can you not add the space when combining them initially?

 

[excelpete1]

Can you not add the space when combining them initially?

Do you mean if i had a space in front of the last name in the right column?

perhaps i'm simply not combining them correctly... At the moment i'm doing: =A1&A2 and then hitting enter, this bring the first and last name together, just without a space.

 

[RoryA]

That’s an easy change then:

=A1&" "&a2

 

[excelpete1]

That’s an easy change then:

=A1&" "&a2

OMG!!! that's sooooooo much better than what my IT guy gave me to work with.

here's what his solution was: =REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

 

[Scott Huish]

 

[Peter_SSs]

OMG!!! that's sooooooo much better than what my IT guy gave me to work with.

here's what his solution was: =REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")

Perhaps you could now apply for his job!

 

[excelpete1]

Perhaps you could now apply for his job!

never! lol!