# Software

## Question

Locked

### Excel Formula

By ·
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.

Thanks,
Kevin

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

### This is similar to parsing phone numbers

by In reply to Excel Formula
Collapse -

### Are the number lengths the same?

by 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 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 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.

GP

Collapse -

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

http://www.emailoffice.com/excel/arrays-bobumlas.html

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

## Related Discussions

• 4

#### Accounting software

beverleycosgrove59 ·

• 5

Nora.Oz ·

• 1

#### Software for generating invoices

Michael_Schumacher ·

• 3