Value manipulation

V

Vic

I need 2 formulas

1. a formula to do the following:
in A1 is 00003056-021
I need B1 like this - if there are leading zeros then remove first 4 zeros
(only 4 and not 5). I need B1 to have 3056021 without the dash.

2. a formula to do the following:
in A1 is 30701267-042
I need B1 like this - if there are no leading zeros then remove digits
5-6-7-8 and the dash. I need b1 to have 3070042.

Now, how do we combine both formulas?

Thank you.
 
L

Luke M

Combine both formulas using an IF function.

=IF(LEFT(A1,1)="0",VALUE(SUBSTITUTE(A1,"-","")),SUBSTITUTE(LEFT(A1,4)&MID(A1,9,LEN(A1)-9),"-",""))
 
G

Gary''s Student

=SUBSTITUTE(IF(LEFT(A1,4)="0000",RIGHT(A1,LEN(A1)-4),LEFT(A1,4) &
MID(A1,9,256)),"-","")
 
V

Vic

Hi Gary,

Your formula worked. Now, I have a little bit more complications.

Sometimes, A1 has value like this 1186-1067-188. In this case (with 2
dashes), I need to pick up 1067 (4-digit number between 2 dashes) and 188, so
the result will be 1067188.

Can this be incorporated into your formula?

Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top