How should I format a cell to see 'aaa 1234 bbb' if a cell value is
'aaa1234bbb' ?


--
5631
------------------------------------------------------------------------
5631's Profile: http://www.excelforum.com/member.php...o&userid=33022
View this thread: http://www.excelforum.com/showthread...hreadid=528465

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)
--
Kevin Backmann


"5631" wrote:


How should I format a cell to see 'aaa 1234 bbb' if a cell value is
'aaa1234bbb' ?


--
5631
------------------------------------------------------------------------
5631's Profile: http://www.excelforum.com/member.php...o&userid=33022
View this thread: http://www.excelforum.com/showthread...hreadid=528465

if your data varies eg you also have 123456xyzabcd987654321

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=528465

On Fri, 31 Mar 2006 02:31:23 -0600, 5631
wrote:


How should I format a cell to see 'aaa 1234 bbb' if a cell value is
'aaa1234bbb' ?

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