Truncating read-in data
 

Ok, two quick questions.

I'm going to read in data from, let's say cells(9,3).Value
where row 9 col 3 contains a customer number that looks like this

1234567ABC

Hovever, the actual customer number I need for everyday use looks like this
01234567

I've declared my variable like this
dim ColumnC As String

First, I have to truncate that value to just the numbers
1234567
That's my first question. How do I do that?

Second, I need to add the Zero on the front.
I think I do would do it like this:

ColumnC = "0" + ColumnC

Is that right?
And, can I do both steps in one function?

Truncating read-in data
 

for the second part: in VBA to concatenate string ("add them together") you
use the & symbol, so it would be
ColumnC = "0" & ColumnC

You can accomplish both steps in one function: this one should do

Function FixCustNo(CustomerNo as String)
Dim i as integer, Result as String
Result = ""
For i = 1 to Len(CustomerNo)
If IsNumeric(Mid(CustomerNo,i,1)) Then _
Result = Result & Mid(CustomerNo,i,1)
Next i
Result = "0" & Result
End Function

--
- K Dales


"sweens319" wrote:

Ok, two quick questions.

I'm going to read in data from, let's say cells(9,3).Value
where row 9 col 3 contains a customer number that looks like this

1234567ABC

Hovever, the actual customer number I need for everyday use looks like this
01234567

I've declared my variable like this
dim ColumnC As String

First, I have to truncate that value to just the numbers
1234567
That's my first question. How do I do that?

Second, I need to add the Zero on the front.
I think I do would do it like this:

ColumnC = "0" + ColumnC

Is that right?
And, can I do both steps in one function?

Truncating read-in data
 

The Val() function automatically returns the numeric portion of any string
that STARTS with digits, such as "123ABC". To answer your question about
appending a leading zero I must first ask if you want to always append
exactly ONE zero regardless of the number of digits read in?

Dim ColumnC As String

ColumnC = "0" & Val(Cells(9, 3).Value)

Or do you want exactly EIGHT digits regardless of the number of digits read
in?

Dim ColumnC As String

ColumnC = Format(Val(Cells(9, 3).Value), "00000000")

ALSO: note the use of "&" not "+" for appending strings, otherwise a math
operation may be performed instead!






"sweens319" wrote:

Ok, two quick questions.

I'm going to read in data from, let's say cells(9,3).Value
where row 9 col 3 contains a customer number that looks like this

1234567ABC

Hovever, the actual customer number I need for everyday use looks like this
01234567

I've declared my variable like this
dim ColumnC As String

First, I have to truncate that value to just the numbers
1234567
That's my first question. How do I do that?

Second, I need to add the Zero on the front.
I think I do would do it like this:

ColumnC = "0" + ColumnC

Is that right?
And, can I do both steps in one function?

Truncating read-in data
 

Thank you very much. Now, pardon me for sounding stupid, but I've only been
working with VBA for a couple of days.

I have read in the number to the variable ColumnC. How does this function
know to use that string variable as the one to change? Or do I have to send
it in?
ie:
Function FixCustNo(ColumnC as String)
or
Function FixCustNo(CustomerNo as String)
CustomerNo = ColumnC
or something similar



"K Dales" wrote:

for the second part: in VBA to concatenate string ("add them together") you
use the & symbol, so it would be
ColumnC = "0" & ColumnC

You can accomplish both steps in one function: this one should do

Function FixCustNo(CustomerNo as String)
Dim i as integer, Result as String
Result = ""
For i = 1 to Len(CustomerNo)
If IsNumeric(Mid(CustomerNo,i,1)) Then _
Result = Result & Mid(CustomerNo,i,1)
Next i
Result = "0" & Result
End Function

--
- K Dales


"sweens319" wrote:

Ok, two quick questions.

I'm going to read in data from, let's say cells(9,3).Value
where row 9 col 3 contains a customer number that looks like this

1234567ABC

Hovever, the actual customer number I need for everyday use looks like this
01234567

I've declared my variable like this
dim ColumnC As String

First, I have to truncate that value to just the numbers
1234567
That's my first question. How do I do that?

Second, I need to add the Zero on the front.
I think I do would do it like this:

ColumnC = "0" + ColumnC

Is that right?
And, can I do both steps in one function?