Software

Ask something
Question Locked

Excel Formula

0 helpful
6 replies

by kfrost

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

6 replies

by ben.rattigan

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

by kfrost

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.

by somethinggood4

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(,4) to get the rightmost four characters (1975). Then to parse the birthdate, I used =LEFT(,5) to get the left five numbers (15/06) then parse THAT equation using =LEFT(,2) and =RIGHT(,2) to get (15) and (06).

Finally, one more to bring the result =CONCATENATE(&" "&&" "&)

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

GP

This discussion is closed to new replies.