Re: Formatting cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Fri, 31 Mar 2006 02:31:23 -0600, 5631
<5631.25izka_1143794101.369@xxxxxxxxxxxxxxxxxxxxx> wrote:


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

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
.

Relevant Pages

  • Re: Text formatted cells displaying numbers in scientific format
    ... When it 'sees' a large number it uses scientific format since ... I accept that Excel can't handle numbers greater than 15 digits and your ... string was 16 digits or more to be converted to a scientific value. ... It just doesn't display correctly in the cell (except after entering ...
    (microsoft.public.excel)
  • Re: Can General format numbers be mistaken as text?
    ... how many digits that may be. ... It's fine if you want to use General format. ... Perhaps your coworker is worried that there may be situations where Excel will change the cell format from General to Text automagically, just as it changes from General to Number, Currency or Percentage. ... mean it will "display whatever the user types" exactly as it is entered. ...
    (microsoft.public.excel.misc)
  • Re: How to change all numbers to xs?
    ... For Each anyCell In Selection ... 'now you can safely convert Digits to x's ... It fairly well preserves the format and layout except when the Accounting ... cell in selection" works without it. ...
    (microsoft.public.excel.misc)
  • Help - Any number longer than 12 digits turns to scientific and ro
    ... I feel like I'm a pretty experienced Excel user but I'm totally baffled by ... function bar gives me a few more digits but not nearly all of them and not ... into the first cell. ... This does not seem to be a "format, ...
    (microsoft.public.excel.misc)
  • =Text(cell,"000000000") in VB
    ... formula to refer to a cell to produce a nine digit text field. ... For instance for column M to format column L, the formula I have seen is ... There must be a better way to format so it always has nine digits and add ... leading zeros rather than truncate. ...
    (microsoft.public.excel.programming)