Add Spaces

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Sep 17, 2016

• #2

How_Do_I said:

Hello, what formula could I use to add a space between letters please... =LEFT(F3,1)&" "&RIGHT(F3,LEN(F3)-1) is working for two letters but I can't work out what formula to use to get this working for 3 letters...

Click to expand...

How many letters maximum will you ever have to do this with?

 

Upvote 0

How_Do_I

Well-known Member

Joined

Oct 23, 2009

Messages

1,831

Office Version

1. 2010

Platform

1. Windows

• Sep 17, 2016

• #3

Hello Rick... I'll work around any more than 3... So I would like to get it working for 3 initials please...

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Sep 17, 2016

• #4

How_Do_I said:

Hello Rick... I'll work around any more than 3... So I would like to get it working for 3 initials please...

Click to expand...

It might not be that simple... how many letters maximum could you have?

 

Upvote 0

T

TonyUK72

Board Regular

Joined

Oct 6, 2015

Messages

220

• Sep 17, 2016

• #5

Rick Rothstein said:

It might not be that simple... how many letters maximum could you have?

Click to expand...

I love the work's of art you come up with, it's a shame when you end up with formulas like this:

Code:

=IF(LEN(F3)=3,LEFT(F3,1)&" "&MID(F3,2,1)&" "&RIGHT(F3,1),LEFT(F3,1)&" "&RIGHT(F3,LEN(F3)-1))

What would you do for say "12 characters" Rick?

 

Upvote 0

How_Do_I

Well-known Member

Joined

Oct 23, 2009

Messages

1,831

Office Version

1. 2010

Platform

1. Windows

• Sep 17, 2016

• #6

I can't give a number Rick, the letters are the initials of people... The formula that TonyUK72 has kindly added is working for people with 1, 2 and 3 initials... Thanks to you both for your assistance so far...

 

Upvote 0

I

István Hirsch

Well-known Member

Joined

May 16, 2013

Messages

1,634

• Sep 17, 2016

• #7

Give this a try up to 4 initials:

=TRIM(REPLACE(REPLACE(REPLACE(F3,2,0," "),4,0," "),6,0," "))

 

Last edited: Sep 17, 2016

Upvote 0

How_Do_I

Well-known Member

Joined

Oct 23, 2009

Messages

1,831

Office Version

1. 2010

Platform

1. Windows

• Sep 17, 2016

• #8

István Hirsch said:

Give this a try up to 4 initials:

=TRIM(REPLACE(REPLACE(REPLACE(F3,2,0," "),4,0," "),6,0," "))

Click to expand...

Thanks for that...

 

Upvote 0

I

István Hirsch

Well-known Member

Joined

May 16, 2013

Messages

1,634

• Sep 17, 2016

• #9

You are welcome.

 

Upvote 0