text extract

J

John S

How do i extract a variable length string of text from a longer string? My
cell reads "Login LDAP user mapatron at 192.168.1.146 as role student", and
would like to extract "mapatron" from this string. this username is variable
in length, so using three columns and "=right(X,X)" and "=left(X,X)" doesn't
work. any suggestions? Thanks in advance for your help.
John Supple
 
F

Frank Kabel

Hi John
any logic WHERE this username is lcated. e.g. always after the string
'LDAP'?
 
J

JohnWS

Hi Frank,

Yes. The username will always be followed by "Login LDAP user "username"".
The username will be of variable length, from 3 to 20 characters, the IP
address will be variable, and the role is variable - it will be one of the
following - student, staff, or faculty.

By the way, I was stationed in Hanau in 1983 and visited Frankfurt quite
often.

Thanks,
John
Oxford, MS
 
R

Ron Rosenfeld

How do i extract a variable length string of text from a longer string? My
cell reads "Login LDAP user mapatron at 192.168.1.146 as role student", and
would like to extract "mapatron" from this string. this username is variable
in length, so using three columns and "=right(X,X)" and "=left(X,X)" doesn't
work. any suggestions? Thanks in advance for your help.
John Supple
Click to expand...

If the user name always follows the word "user" and ends at a space, you could
use the formula:

=MID(TRIM(A1),FIND("user",TRIM(A1))+5,FIND(
" ",TRIM(A1),FIND("user",TRIM(A1))+5)-
FIND("user",TRIM(A1))-5)

If there is always going to be just one space between words, you can omit the
TRIM function and simplify a bit to:

=MID(A1,FIND("user",A1)+5,FIND(
" ",A1,FIND("user",A1)+5)-
FIND("user",A1)-5)


--ron
 
F

Frank Kabel

Hi
then try:
=MID(A1,FIND("user",A1)+5,FIND("
",A1,FIND("user",A1)+5)-FIND("user",A1)-5)
 
Top