how to nest REPLACE function in Excel?, (syntax issue) 1

[robFSS]

Hello -

I just learned about the Replace function.
pretty slick for adding a hyphen in a specific spot in a string of numbers.

e.g.

[b]     A1              B1[/b]
   123456        =REPLACE(A1,4,0,"-")

this makes cell B1 read "123-456".

awesome.

now, how can I use the REPLACE function 2x (or more) in cell B1.

Assume that now my number is 123456789, and I want to make it read as "123-456-789"

I tried using a comma between replace statements but that gives an error.

I'd like to accomplish this all neat and tidy in one cell if possible, (rather than use REPLACE over and over again in multiple cells).

Any ideas?

 

[AnotherHiggins]

An option available to you, assuming that your data is currently stored as numbers, is to just use a custom format.

Select the column, go to Format > Cells > Number > Custom and in the top box at the right, type in 000-000-000

This will still store the data as numbers (123456789) but will display with hyphens (123-456-789).

But if these are some kind of ID numbers (with which you will never do math), then I think it is best to store them as text.

If you will always have 9 digits, I'd just use a combination of the LEFT, MID and RIGHT, as follows:

[COLOR=blue white]=left(A1, 3) & "-" & mid(A1, 4, 3) & "-" & right(A1,3)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi orlotron:

try ...

=REPLACE(REPLACE(A1,4,0,"-"),8,0,"-")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[robFSS]

Yogi -

Thanks, that's just what I was looking for!

John -
Your suggestion might actually work even better to satisfy my requirement. Although it depends on whether or not I can apply that kind of formatting in a sharepoint based excel list. here's another question building on your suggestion:
let's say I actually need three dashes.
e.g. 5340-01-123-4321

Can I accomplish this formatting with left, right, and mid?

thanks,
Rob

 

[AnotherHiggins]

Sure. It is just one more MID statement thrown in there.

something like this:

=left(A1, 4) & "-" & mid(A1, 5, 2) & "-" & mid(A1, 7, 2) & "-" & right(A1,4)

But that will only work if every cell contains 13 characters.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.