Return string using RIGHT function

  • Thread starter Thread starter Bird Byte
  • Start date Start date
B

Bird Byte

I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last names
was no problem). One problem is that some names were entered as last, first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma or
leading space).

Thanks for any help!
 
Bird Byte said:
I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last
names
was no problem). One problem is that some names were entered as last,
first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma
or
leading space).

Thanks for any help!
Click to expand...


The Mid$() function will give you everything after the comma, and then you
can use the Trim() function to strip off any leading space. Here's an
example I tested in the Immediate window ...

? Trim(Mid$("some, text", InStr(1, "some, text", ",") + 1))
text
 
Right wasn't the right function to use.

To get what's after the comma, use

FName: Mid([FullName],InStr([FullName],",") + 2)

or

FName: Trim(Mid([FullName],InStr([FullName],",") + 1))

To get what's before the comma, use

LName: Left([FullName],InStr([FullName],",") - 2)
 
Thanks for the ideas! This approach omitted the first letter when there was
no space after the comma. Brendan Reynolds' solution (using mid and instr
functions then trimming) worked like a charm.
Thanks very much!

KARL DEWEY said:
Try this --
FName: Right([FullName],Len([FullName])-(InStr([FullName],",")+1))

--
KARL DEWEY
Build a little - Test a little


Bird Byte said:
I have a field where a name was supposed to be entered as Last Name, First
Name. Now I have to separate out the first names (extracting the last names
was no problem). One problem is that some names were entered as last, first
and others were entered last,first - without a space.
The following worked with marginal results:

FName: Right([FullName],InStr([FullName],","))

Some results were fine, while others looked like this:
LName FName FullName
Dummdorfe rfe, Wayne Dummdorfe, Wayne
Nice opher Nice, Christopher
Hassles Charlie Hassles, Charlie

As you can see, some strings returned as wanted, while others didn't.
Any ideas how to get the correct string (entire first name with no comma or
leading space).

Thanks for any help!
Click to expand...
Click to expand...
 
Back
Top