slightly complicated substitute function

[MPSingley]

hey, i need to make these values in A1:A5

22-000-104-011
22-000-104-012
22-000-104-013
22-000-122-001

into these values in E1:E5

000104011
000104012
000104013
000122001
.........
without using multiple rows for seperate functions

combination of substitute functions?? I can't get it to work
properly.. Any help would be appreciated!

-Matt

 

[Max]

One way which might suffice ..

In E1:
=MID(SUBSTITUTE(A1,"-",""),3,99)
Copy down to E5

 

[Max]

Think this multi-cell array version of the earlier could also do it
Select E1:E5, put into the formula bar, then array-enter with CSE*:
=MID(SUBSTITUTE(A1:A5,"-",""),3,99)
*press CTRL+SHIFT+ENTER to confirm the formula

---

 

[MPSingley]

Hey thank you very much, with your help i got it to work perfectly...

I went with this route though..

=SUBSTITUTE(SUBSTITUTE(A5,"22-",""),"-","")

 

[Pete_UK]

But doesn't that change your final number to:

0001001 ?

Pete

 

[MPSingley]

no sir, it just takes out the "22-" and then any additional "-"

 

[Max]

A bit puzzling, as Pete pointed out ..

Your equation:

=SUBSTITUTE(SUBSTITUTE(A5,"22-",""),"-","")

won't work for the last sample data in A5, ie:
22-000-122-001

It'll return in E5 as: 0001001
while you wanted: 000122001 ??

---

 

[MPSingley]

ah yes, i see your point

i now went with

=SUBSTITUTE(REPLACE(A5,1,3,""),"-","")

 

[Max]

Ahh, good. your new one's ok. Do go as preferred.