Help needed stripping numbers from name fields

[Sausagefingers]

Hi,
I am looking at a whole bunch of example modules trying to find one that fits my needs. I'm a little stumped and could use somebodys help!!

What I have is a large .xls spreadsheet that I have to link to an access 2k db for address label printing.

The spreadsheet contains customer data and for the most part, I don't have any problems with it.

However, the customer name field also contains the customer telephone number!

The name is separated from the number via a single space in the following format (for example): MR I CHUCKLEBERRY 0123456789(H)

I have to strip (using vb/module) the number from the name, hopefully, from the first blank space encountered starting from the end. How is this possible? I would also have to deal with the pesky bracketed single character at the end too. The resulting (stripped) customer name will then go on to form the first line of a customer shipping label.

Can someone help me with this please, I'm bashing my head against a wall here!

Thanks in advance.

 

[Rabbie]

You could try using INSTR to find the first 0(zero) character and then use a LEFT function to extract the name. See Access help for more info on these functions

 

[chergh]

I'd go with the split function here something like:

dim strVar as variant
dim strName as string
Dim strPhone as string

strVar = split(worksheets("sheetName").range("A1").value, " ")

for i = lbound(strVar) to ubound(strVar)
   
   if isnumeric(left(strVar(i),1)) then
       strPhone = strVar(i)
   else
       strName = strName & " " & strVar(i)
   end if

next i

strPhone = left(strphone,instr(1,strPhone,"(") -1)

worksheets("SheetName").range("B1").value = ltrim(strName)
worksheets("SheetName").range("C1").value = strPhone

Something along these lines should work.

 

[DCrake]

To find the position of a specific character reading right to left use the fucntion InStrRev()

In you example

X = InStrRev("MR I CHUCKLEBERRY 0123456789(H)"," ")
X = 18

So to extract the persons name you would need the fomula

Person = Trim(Left(xlName,InStrRev(xlName," "))

 

[Sausagefingers]

Thanks for your time and the superquick responses. I will let you know how I get on!

 

[Rabbie]

This does the job for you

left(StrName,instr(1,Strname,"0")-1)

It works because telephone numbers start with a Zero that shouldn't occur in a name

 

[Sausagefingers]

This does the job for you

left(StrName,instr(1,Strname,"0")-1)

It works because telephone numbers start with a Zero that shouldn't occur in a name

Yes indeed. It certainly does!

Thanks

 

[Rabbie]

Gladd to have been of help