Stripping and Adding Characters of a String

[mwooster21]

I have a speadsheet with about 3000 rows. In column C there are phone numbers listed like this:
3????|0|61421
464??|1|312954

The number between || says how many digits to strip from the right most digit to the right of the ||. The numbers on the left get added onto the end.

so these would translate into:
614213????
31295464??

Is there any code I could use to automatically change these?

Thanks!

Edit: I am on Windows XP with Excel 2007

 

[poolhall]

try this:

=MID(C1,SEARCH("|",C1)+3,LEN(C1)-MID(C1,SEARCH("|",C1)+1,1))&LEFT(C1,SEARCH("|",C1)-1)

 

[Matty]

Perhaps:

=MID(C1,FIND("|",C1)+3,LEN(C1)-(FIND("|",C1)+2)-MID(C1,FIND("|",C1)+1,FIND("|",C1,FIND("|",C1)+1)-1-FIND("|",C1)))&LEFT(C1,FIND("|",C1)-1)

Note: This will only work if the value between the "| |" is a single digit.

Matty

 

[mwooster21]

Close!
That worked for the ones with |0| but it didn't strip the last digit when it was |1|. (all the middle values are just 0 and 1). The digit that gets stripped off appears to always be the same number as the first digit on the left that gets added on anyways. The final number just has to be 10 digits.

Edit: that was for poolhall

Matty - yours did the trick. thanks both of you!

 

[Matty]

Close!
That worked for the ones with |0| but it didn't strip the last digit when it was |1|. (all the middle values are just 0 and 1). The digit that gets stripped off appears to always be the same number as the first digit on the left that gets added on anyways. The final number just has to be 10 digits.

Did you try my proposal?

Btw, how rude of me not to Welcome you to the Board! Welcome!

Matty

 

[mwooster21]

Thank you I'm sure I will be back here fairly often.

 

[poolhall]

I've got the mistake. SHould be

=MID(C2,SEARCH("|",C2)+3,LEN(C2)-SEARCH("|",C2)-2-MID(C2,SEARCH("|",C2)+1,1))&LEFT(C2,SEARCH("|",C2)-1)