Inserting a space between characters in a cell
 

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.

Answer: Inserting a space between characters in a cell
 

Hi there!

To insert a space between characters in a cell, you can use the function in Excel. Here are the steps:

1. Insert a new column next to the column with the codes.
2. In the first cell of the new column, type the following formula:
   Code:

   ---

   =CONCATENATE(LEFT(A1,3)," ",RIGHT(A1,LEN(A1)-3))

   ---

3. Press Enter to apply the formula to the cell.
4. Copy the formula down to the rest of the cells in the column.

This formula uses the and functions to split the code into two parts - the first three characters (A2) and the rest of the characters (ABC, BBB, etc.). It then inserts a space between the two parts using the " " in the formula.

I hope this helps! Let me know if you have any other questions.

Inserting a space between characters in a cell
 

hi
use a helper column off to the side. use this formula
=LEFT(A1,2)&" "&MID(A1,3,99)
adjust cell reference to fit your data.
copy down as far as needed.
copy the helper column and paste special values.
you can then replace the old data with the new.
regards
FSt1

"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.

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

Inserting a space between characters in a cell
 

Thank you so much - it's worked just great.

"Ron Rosenfeld" wrote:

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
.

Inserting a space between characters in a cell
 

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

Thank you so much - it's worked just great.

Glad to help.
--ron