alpha characters in a formula

[TeeTee]

I am trying to set up an inventory report for items which have serial
numbers, but have little experience with complicated formulas. The three
columns of the worksheet contain the starting number, the ending number and
the total in stock for each series. However, most of the serial numbers
contain letters, for example:

S2M478 S2M489 12
XV918N XV999N 82
DG12325 DG12339 15
9B2J8 9B9J9 72
AB280CC AB309CC 30

Is there a formula that will work with both numbers and letters? I’d
appreciate any help. Thanks.

 

[Glenn]

I am trying to set up an inventory report for items which have serial
numbers, but have little experience with complicated formulas. The three
columns of the worksheet contain the starting number, the ending number and
the total in stock for each series. However, most of the serial numbers
contain letters, for example:

S2M478 S2M489 12
XV918N XV999N 82
DG12325 DG12339 15
9B2J8 9B9J9 72
AB280CC AB309CC 30

Is there a formula that will work with both numbers and letters? I’d
appreciate any help. Thanks.

A formula to do what?

 

[TeeTee]

Glenn, I need the formula to indicate the total number of items in that
series, i.e., between the starting series number (S2M478) and the ending
series number (S24890). The third column I added below indicates the correct
number of items, but it has been calculated manually. I'm hoping a formula
will indicate the same total.

 

[Rick Rothstein]

What is the sequence of numbers between 9B2J8 and 9B9J9 (you don't have to
list them all, but give us an idea how they "count" up)? Also, where is the
data that you want counted (that is, how is your data laid out)?

 

[Ashish Mathur]

Hi,

Unfortunately there is no consistency in the alphanumeric string I.e. the
numeric portion is of variable length, the numeric portion does not appear
together.

We would have been able to formulate a solution if the numeric strings
appeared together.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

What is the sequence of numbers between 9B2J8 and 9B9J9

Could be either 928 to 999 or 28 to 99.

I'll pass on this one! <g>

 

[JBeaucaire]

Here's an approach that seems to work with all your samples. First you need
to install a UDF that "filters" your strings properly. Here's the code for it:
===========
Function CleanAll(txt As String) As String
'base code by Jindon, MrExcel.com MVP
With CreateObject("VBScript.RegExp")
'enter all characters to be stripped out into the pattern
'Use the ^ at the beginning to indicate inclusive, leave it out to list
deletions
' .Pattern = "[^0-9]" 'leaves only numbers
' .Pattern = "[^A-Z\s]+" 'leaves only letters and spaces
.Global = True
.IgnoreCase = True
CleanAll = Application.Trim(.replace(txt, ""))
End With
End Function
===========

Press Alt-F11 to open the VBEditor
Click Insert > Module
Paste in all the code above between the ======
Alt-F11 to close the Editor.
Save your sheet.

CleanAll() setup the way that it is in that code will only leave number. So
you can use your new function like so:

A1 = 9B2J8
B1 = 9B9J9
C1 = formula: =Cleanall(B1)-Cleanall(A1)+1

I used that on every example you started with and got the correct answers
all the way down.

Hope this works for you.

Jerry

 

[Rick Rothstein]

Function CleanAll(txt As String) As String

'base code by Jindon, MrExcel.com MVP
With CreateObject("VBScript.RegExp")
'enter all characters to be stripped out into the pattern
'Use the ^ at the beginning to indicate inclusive, leave it out to list
deletions
' .Pattern = "[^0-9]" 'leaves only numbers
' .Pattern = "[^A-Z\s]+" 'leaves only letters and spaces
.Global = True
.IgnoreCase = True
CleanAll = Application.Trim(.replace(txt, ""))
End With
End Function

This is how I would write your above function without using the RegExp
object (the code structure is identical for both functions; only the Like
operator's "pattern statement" inside the For..Next loop is different)...

' Leave only numbers
Function CleanAll(ByVal Txt As String) As String
Dim X As Long
For X = 1 To Len(Txt)
If Mid(Txt, X, 1) Like "*[!0-9]*" Then Mid(Txt, X, 1) = Chr(1)
Next
CleanAll = Replace(Txt, Chr(1), "")
End Function

' Leave only letters and spaces
Function CleanAll(ByVal Txt As String) As String
Dim X As Long
For X = 1 To Len(Txt)
If Mid(Txt, X, 1) Like "*[!A-Za-z ]*" Then Mid(Txt, X, 1) = Chr(1)
Next
CleanAll = Replace(Txt, Chr(1), "")
End Function

--
Rick (MVP - Excel)

Here's an approach that seems to work with all your samples. First you
need
to install a UDF that "filters" your strings properly. Here's the code for
it:
===========
Function CleanAll(txt As String) As String
'base code by Jindon, MrExcel.com MVP
With CreateObject("VBScript.RegExp")
'enter all characters to be stripped out into the pattern
'Use the ^ at the beginning to indicate inclusive, leave it out to list
deletions
' .Pattern = "[^0-9]" 'leaves only numbers
' .Pattern = "[^A-Z\s]+" 'leaves only letters and spaces
.Global = True
.IgnoreCase = True
CleanAll = Application.Trim(.replace(txt, ""))
End With
End Function
===========

Press Alt-F11 to open the VBEditor
Click Insert > Module
Paste in all the code above between the ======
Alt-F11 to close the Editor.
Save your sheet.

CleanAll() setup the way that it is in that code will only leave number.
So
you can use your new function like so:

A1 = 9B2J8
B1 = 9B9J9
C1 = formula: =Cleanall(B1)-Cleanall(A1)+1

I used that on every example you started with and got the correct answers
all the way down.

Hope this works for you.

Jerry



--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

Hi,

Unfortunately there is no consistency in the alphanumeric string I.e. the
numeric portion is of variable length, the numeric portion does not
appear
together.

We would have been able to formulate a solution if the numeric strings
appeared together.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[TeeTee]

My sincere thanks to everyone who took the time and made the effort to put me
in the right direction, and for solving my problem for me. I am eternally
grateful and, no doubt, will be troubling you again in the future on another
subject.

All the best!
TeeTee