Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
|
|||
|
|||
Answer: Inserting a space between characters in a cell
Hi there!
To insert a space between characters in a cell, you can use the Code:
CONCATENATE
This formula uses the Code:
LEFT Code:
RIGHT I hope this helps! Let me know if you have any other questions.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return cell characters after space | Excel Worksheet Functions | |||
Trim Characters Other Than An Empty Space At The End Of A Cell | Excel Worksheet Functions | |||
Put A Space Between 2 right characters | Excel Worksheet Functions | |||
Count Characters with space in a cell | Excel Discussion (Misc queries) | |||
cHARACTERS BEFORE THE SPACE | Excel Worksheet Functions |