Another Simple Excel Problem

[tvisgod]

I have a column of values resembling the following:

ssib0000354
ssib0023452
ssj0000234
ssib0008744222
ssj022
...

I need a function that will:
1. move the alpha characters from the beginning to the end
2. eliminate the zeroes at the beginning (whether there are one or two or five, etc.)

The number of zeroes varies, though it's probably always between one and six. The number of digits after the initial zeroes varies, though again it's always probably between one and nine digits long. The alpha characters at the beginning are always either "ssib" or "ssj" ... there is no other variation.

So what I'm looking for is a function that will turn the above to:

354ssib
23452ssib
234ssj
8744222ssib
22ssj

Any help would be appreciated.

 

[Aladin Akyurek]

I have a column of values resembling the following:

ssib0000354
ssib0023452
ssj0000234
ssib0008744222
ssj022
...

I need a function that will:
1. move the alpha characters from the beginning to the end
2. eliminate the zeroes at the beginning (whether there are one or two or five, etc.)

The number of zeroes varies, though it's probably always between one and six. The number of digits after the initial zeroes varies, though again it's always probably between one and nine digits long. The alpha characters at the beginning are always either "ssib" or "ssj" ... there is no other variation.

So what I'm looking for is a function that will turn the above to:

354ssib
23452ssib
234ssj
8744222ssib
22ssj

Any help would be appreciated.

B2, just enter and copy down:

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0&
    LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

If you would use an auxiliary cell...

B2, just enter and copy down:

=REPLACE(A2,1,C2,"")+0&LEFT(A2,C2)

C2, just enter and copy down:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1

 

[doubledaffy]

If all of the initial values are in Column A starting at A1, give this a shot in column B:

=VALUE(RIGHT(A1,LEN(A1)-FIND(0,A1)+1))&LEFT(A1,FIND(0,A1)-1)

 

[tvisgod]

doubledaffy:

Thank you, that is working perfectly.

Is there any chance you'll take a minute to explain the function you've created to me and how each component works to do what it does?

 

[doubledaffy]

Sure. I'll explain the logic to you, and you can look at the excel help files to see how the functions work.

1. Use FIND to see what position the "0" is at in the string.
2. Use LEN to get the length of the string
3. Get X characters beginning at the RIGHT of the string. Where X is the length of the string minus the position of the first "0," plus 1 so that it's inclusive. Essentially crop off the first alphabetic characters.
4. Convert the cropped string to a VALUE thereby eliminating the 0's at the front (because excel automatically gets rid of zeroes at the beginning of values)
5. Append the first Y characters from the LEFT of the original string to the end of our newly created string. Where Y is again the position of the first 0, minus 1 so that the 0 itself isn't included.

Hope that clears it up.

Oh, and when I deal with strings like this, I usually do each step in a separate cell so that I can see how it works out. Then, once I get each part working, I put them together into one formula. It's just easier for me to think about that way.

 

[Aladin Akyurek]

doubledaffy:

Thank you, that is working perfectly.

Is there any chance you'll take a minute to explain the function you've created to me and how each component works to do what it does?

Does it also work for something like:

ssib8740

or you don't have such entries?