Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable template is truncating data | Excel Discussion (Misc queries) | |||
Linked cell truncating data | Excel Discussion (Misc queries) | |||
Truncating data in drop down list | Excel Worksheet Functions | |||
truncating data within a cell for an entire column | New Users to Excel | |||
Exported File truncating cell data after 255 characters | Excel Discussion (Misc queries) |