Excel Formula

By kfrost ·
Good afternoon,

I'm trying to write an Excel formula that will take off 2 numbers and add spaces. I need 0751489 to look like 5 14 89. I have about 100 cells that are different but I wanto apply this general formula. Any help will be greatly appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

This is similar to parsing phone numbers

by robo_dev In reply to Excel Formula
Collapse -

Are the number lengths the same?

by ben.rattigan In reply to Excel Formula

Are the numbers consistent in length?, if so you could use concatenate.

E.g. =CONCATENATE(MID(cellreference,3,1)," ",MID(cellreference,4,2)," ",RIGHT(cellreference,2))

Collapse -

Excel Formula

by kfrost In reply to Are the number lengths th ...

The lengths are not the same. Some look like 202040 which need to look like 20 20 40 other look like 82401PEB and need to look like 8 24 01PEB.

Collapse -

Try "LEFT" and "RIGHT"...

by somethinggood4 In reply to Excel Formula

I had the same problem trying to calculate age based on birthdate. What I did was take the text string "15/06/1975" and use =RIGHT(<CELLREF>,4) to get the rightmost four characters (1975). Then to parse the birthdate, I used =LEFT(<CELLREF>,5) to get the left five numbers (15/06) then parse THAT equation using =LEFT(<RESULTREF>,2) and =RIGHT(<RESULTREF>,2) to get (15) and (06).

Finally, one more to bring the result =CONCATENATE(<RESULT1>&" "&<RESULT2>&" "&<RESULT3&gt

It's a long process, but this can be modified for whatever string you need.


Collapse -

An array formula is what you are looking for...

Please post back if you have any more problems or questions.

Related Discussions

Related Forums