View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Inserting a space between characters in a cell

On Thu, 22 Apr 2010 18:01:01 -0700, mrmnz
wrote:

Hi

I have a column of codes and some are written as below:

A2ABC
A2BBB
A2CBC
A2DBD

I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.

What is the best/easiest way to do this?

Thanks.


You could use Find/Replace

Select the cells that start with A2
Find/Replace
Find what: A2
Replace with: A2<space

You could use a formula:

=SUBSTITUTE(A1,"A2","A2 ",1)

On the other hand, if you always want to insert a <space between the 2nd and
3rd characters, you could use:

=REPLACE(A1,3,0," ")

And if you always want to insert a <space after the first digit, which might
or might not be in the second position:

=REPLACE(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2 ,3,4,5,6,7,8,9,0"))+1,0," ")

--ron