Formatting cells

K

Kevin B

This would not be a formatting issue, but if the data is same throughout the
column you could parse the data and concatenate it to look the way you want
it to.

The following formula parses the data in A1 an inserts spaces after the 3rd
and 7th characters:

=LEFT(A1,3)&" "&MID(A1,4,3)&" "&RIGHT(A1,3)
 
R

robert111

if your data varies eg you also have 123456xyzabcd987654321

you could make a lookup table to determine if each character is
number or a letter. Then test for where number becomes letter or vic
versa, make a note of character number and then proceed as previou
answe
 
R

Ron Rosenfeld

How should I format a cell to see 'aaa 1234 bbb' if a cell value is
'aaa1234bbb' ?:confused:
Click to expand...

Only numbers can be formatted.

If you want to insert spaces between strings of letters and strings of digits,
you would have to use formulas.

Here's one method that might work for you. It assumes that your strings
consist only of letters or digits, and that you want a space between wherever
they change.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/forums

Then use this **array-entered** formula:

=MCONCAT(REGEX.MID(A1,"[A-Za-z]+|\d+",ROW(
INDIRECT("1:"&REGEX.COUNT(A1,"[A-Za-z]+|\d+"))))," ")

To **array-enter** a formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.


--ron
 
Top