combining =SEARCH & =IF statements

hello, I have a simple task i think, but i need some guidance!

I have 3 Cells:
A1: 12345 - 67891
A2: 12345-67891
A3: 1234567891

Here are the things I want to do:
1) Remove all spaces
2)IF the cell already has a hyphen, do nothing
3)IF the cell does not have a hyphen, insert one at approximately 5
character positions from the right.

I know how to do all of these in their own cells, but I want to combine all
of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks
like this:

A1: 12345-67891
A2: 12345-67891
A3: 12345-67891

Thank you in advance!

In B1: =REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",),"-",),6,,"-")

copy down


"McRibIsBack" wrote:

hello, I have a simple task i think, but i need some guidance!

I have 3 Cells:
A1: 12345 - 67891
A2: 12345-67891
A3: 1234567891

Here are the things I want to do:
1) Remove all spaces
2)IF the cell already has a hyphen, do nothing
3)IF the cell does not have a hyphen, insert one at approximately 5
character positions from the right.

I know how to do all of these in their own cells, but I want to combine all
of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks
like this:

A1: 12345-67891
A2: 12345-67891
A3: 12345-67891

Thank you in advance!

hey thanks! For not having teeth, your formulas carry quite a bite. I've
never nested functions like these, so I'll stare at this for a bit so I can
understand it. Works brilliantly, thanks again!!!!

"Teethless mama" wrote:

In B1: =REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",),"-",),6,,"-")

copy down


"McRibIsBack" wrote:

hello, I have a simple task i think, but i need some guidance!

I have 3 Cells:
A1: 12345 - 67891
A2: 12345-67891
A3: 1234567891

Here are the things I want to do:
1) Remove all spaces
2)IF the cell already has a hyphen, do nothing
3)IF the cell does not have a hyphen, insert one at approximately 5
character positions from the right.

I know how to do all of these in their own cells, but I want to combine all
of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks
like this:

A1: 12345-67891
A2: 12345-67891
A3: 12345-67891

Thank you in advance!

hey only one small thing. I need the hyphen inserted from 5 character
positions from the right, but this inserts it 5 from the left.

A1: 123-45675 is being turned into: 12345-675.

Can I toss an IF or SEARCH statement in there to do nothing if a "-" is
found?

thanks again

"Teethless mama" wrote:

In B1: =REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",),"-",),6,,"-")

copy down


"McRibIsBack" wrote:

hello, I have a simple task i think, but i need some guidance!

I have 3 Cells:
A1: 12345 - 67891
A2: 12345-67891
A3: 1234567891

Here are the things I want to do:
1) Remove all spaces
2)IF the cell already has a hyphen, do nothing
3)IF the cell does not have a hyphen, insert one at approximately 5
character positions from the right.

I know how to do all of these in their own cells, but I want to combine all
of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks
like this:

A1: 12345-67891
A2: 12345-67891
A3: 12345-67891

Thank you in advance!

=REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",),"-",),LEN(SUBSTITUTE(SUBSTITUTE(A1,"
",),"-",))-4,,"-")


"McRibIsBack" wrote:

hey only one small thing. I need the hyphen inserted from 5 character
positions from the right, but this inserts it 5 from the left.

A1: 123-45675 is being turned into: 12345-675.

Can I toss an IF or SEARCH statement in there to do nothing if a "-" is
found?

thanks again

"Teethless mama" wrote:

In B1: =REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",),"-",),6,,"-")

copy down


"McRibIsBack" wrote:

hello, I have a simple task i think, but i need some guidance!

I have 3 Cells:
A1: 12345 - 67891
A2: 12345-67891
A3: 1234567891

Here are the things I want to do:
1) Remove all spaces
2)IF the cell already has a hyphen, do nothing
3)IF the cell does not have a hyphen, insert one at approximately 5
character positions from the right.

I know how to do all of these in their own cells, but I want to combine all
of the =SEARCH, =IF, =SUBSTITUTE/REPLAC etc. into one cell so the data looks
like this:

A1: 12345-67891
A2: 12345-67891
A3: 12345-67891

Thank you in advance!