1. Registered User
Join Date
Feb 2009
Posts
19

Halo Pro...

I've some puzzle to work with, but i don't know how to write it in vba.

Ok,i've a set of numbers : 5439 (actually 0000 - 9999)

1st.
i want to extract the '5439' (data input) to be :9345

5439 ----> 9345

2nd
i want to seperate 9345 into three set of data.

Data set 1 : 9
Data set 2 : 3
Data set 3 : 45

-----scenario-----
Ok. Assume that i input a set of data = 5439

with vba help, 4 set new data will be produced.

Set data 1 = 9345
Set data 2 = 9
Set data 3 = 3
Set data 4 = 45

That it, could pro help me with this? Thank you pro...

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so you want to meddle with an existing 4 digit number and reformat it, then you want to split it again for some purpose

To renumber I'd suggest a VBA function
eg
Code:
```public function ReformatString(aVariant as variant) as string
'this function chops up the supplied string representation of a number (avariant)
'and returns a mangled version of the original
ReformatString = "" 'set up our default return condition
if we have crap data then return the compliment, crap out
if isnull(avariant) or len(avariant)<>4 then exit function
'ok so we want to make string 1234 = 4321
returnstring = right\$(avariant,1) & mid\$(avariant,3,1) & & mid\$(avariant,2,1) & left\$(avariant,1)
end function```
Code:
```public function GetElement(aVariant as variant, index as integer) as string
'this function rreturns the specified element from a supplied 4 digit string representation fo a number
GetElement = "" 'set up our default return condition
if we have crap data then return the compliment, crap out
if isnull(avariant) or len(avariant)<>4 then exit function
'the element can be only 1 of 4 types
select case index
case 1 ' then we want to reformat the the number
GetElement= right\$(avariant,1) & mid\$(avariant,3,1) & & mid\$(avariant,2,1) & left\$(avariant,1)
case 2 'then we want the 4th digit
GetElement = mid\$(avariant,4,1)
Case 3 'then we want the 3rd digit
getelement = mid\$(avaraint,3,1)
Case 4:'the we want digits 1 & 2
get element = mid\$(avariant,2,1) & mid\$(avariant,1,1)
'case else
'do nothing, we have already set our default return condition
end select
'jobsagoodun....
end function```
to call the function do so with the number required. if its currently an integer then use the format
eg getelement(format(mynumber,"0000"),1)
it is your responsibility to make sure the you check the return of the function. if it returns an empty string then you have supplied duff data, it is up to the calling code to work out how to fix that problem

3. Registered User
Join Date
Feb 2009
Posts
19

## forget to mention...

Halo pro, thanks for your attention...

Yes... could you plz upload a sample... i am bit confussed.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692

call the function with your data
Set data 1 = 9345
Set data 2 = 9
Set data 3 = 3
Set data 4 = 45
lets assume
your data is in a column called myColumn
you want the reformatted data in a series of varairables called myVaraiable

'check if the data is valid, all we can check on is that the source data
'make sure the data is not null and is exactly 4 characters long
if not isnull(mycolumn) and len(mycolumn)=4 then
myVariable1=getelement(mycolumn,1)
myVariable2=getelement(mycolumn,2)
myVariable3=getelement(mycolumn,3)
myVariable4=getelement(mycolumn,4)
else 'else set the destination variable to be empty strings
myvariable1=""
myvariable2=""
myvariable3=""
myvariable4=""
end if

The reformat string does a simple manipulation that fucntionally is the equivalent of.
ReformatString(mycolumn) is fnctionally the same as myVariable1=getelement(mycolumn,1)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•