Returning Just numeric values in a cell. 1

[Cordury]

I have a column of data with account numbers, most contain a letter or two in them. Now, the number of characters in each account number vary from 8 to 30. The text included in the account numbers are not set at a specific space, i.e the first character or the 7th.

Is there a way/formula that will just return all the actual numbers?

For example:
B192634740307A but I just want to return 192634740307

Thanks,
Cord

 

[bluedragon2]

Will this help you out:

Function cvt(acct As String) As String
For i = 1 To Len(acct)
z = Mid(acct, i, 1)
Select Case z
Case "0" To "9"
cvt = cvt + z
Case Else
End Select
Next i
End Function



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Cordury]

BD,
Do I add your code inbetween the Sub and End sub? I am confused?

By the way sorry it took me so long to reply, I had another project or 3 pop up!
Thanks,
Max

 

[bluedragon2]

This is a user defined function.

Open VB editor by hitting Alt F11

dbl click on Workbook on the left side

paste the function as is

This is a user defined function (and I am new to making these, so Skip will probably have to modify it some )

Now, once you have it copied, lets say your acct is in A1, in Cell B1 put:

=cvt(A1)

and it should convert it to all numbers.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Cordury]

I am still getting a #NAME? error.

 

[SkipVought]

Cordury,

In the VB Editor, you must insert a MODULE and paste BLUE'S function into the Module.

In the VB Editor Insert/Module



Skip,

http://www.TheOfficeExperts.com

 

[Cordury]

I know and have done so. I even pasted into a workbook I have saved in the XLSTART folder so my macros are ready to go every time I open Excel from my pc.

 

[Zathras]

Do not "double-click on Workbook on the left side"

The code must go into a "module"

From the VBA editor menu: Insert/Module
then paste the code in to the module workspace.

And in case the Editor Option "Require Variable Declaration" is ticked, you should declare the variables as in this example (uses If instead of Select Case, but the effect is the same.)
[blue]

Function NumbersOnly(Value As String) As String
Dim i As Integer
Dim z As String
  For i = 1 To Len(Value)
    z = Mid(Value, i, 1)
      If z >= &quot;0&quot; And z <= &quot;9&quot; Then
        NumbersOnly = NumbersOnly + z
      End If
  Next i
End Function

[/color]
Note: It is preferable to use a meaningful name. "cvt" is a little too vague for my taste. Also "acct" is a little too specific for what the function is actually doing.

 

[bluedragon2]

Once again, Skip is saving my behind....

Thanks Skip

Has vague variables
|
|
V


Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

You should see the perl script I am writting (talk about vague variables )

Actually, I am getting better... just hate typing $monthplusone over and over P-)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Cordury]

And you thought this thread was dead....

I still never was able to get this to work. I went to the VBA Editor and entered a module.

I pasted the VBA:
Function cvt(acct As String) As String
For i = 1 To Len(acct)
z = Mid(acct, i, 1)
Select Case z
Case "0" To "9"
cvt = cvt + z
Case Else
End Select
Next i
End Function

I then saved the module and attempted to use the formula =cvt(Cell Ref) and still returned a #Name error?

 

[SkipVought]

Cord,

Works just fine!

You stated, "I then saved the module..."

You can't SAVE a module. You can save the workbook that the module resides in.

Is the module in the same workbook that are using this function in?

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Cordury]

Hi Skip,

All my macros are saved in my personal workbook in the XLSTART folder on my C: Drive.

I have no idea why I keep getting an error???

 

[SkipVought]

oops,

Memory leak...

Is the module in the same workbook that YOU are using this function in?

I know that I am using a preposition to end a sentence WITH. ;-)

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[SkipVought]

Is your Personal.xls, Referenced in the VBA Editor -- Tools/References...???

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Cordury]

Thank you, Thank you, Thank you. That was the problem. I did not have it in the workbook I was using. I had it in my personal workbook.

Thank you for your help as well as your patience!

 

[SkipVought]

Ain't it sumthin'!

Here it is, nearly a YEAR later! ;-)

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Cordury]

I do not see my Personal.xls under Tools/References. How can I select my Personal workbook?

 

[SkipVought]

Browse for it.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Cordury]

Name conflicts with existing module, project or object library