Extracting characters from a string

Hi there,

I am trying to extract a number of character from a string. The format of the string is as follows:

First Name (space) Last Name (space) RR (cost centre number) (space) (date)

So an example is as follows:

JOHN CITIZEN RR533 (26.02.07 TO 25.05.07)

I need to extract all characters before the RR, so basically the first and last name of the person. I would really appreciate if anyone could let me know how to do it.

I cannot use the left or right function or even the len funtions as the number of characters in the string vary. The number of characters to the right of "RR" are also non consistent unfortunately.

Any sort of help would be great!

Cheers!

Thanks Jason that was quick.

Is there a way I can actually suppress the errors, for example when it returns #value

Cheers for your help mate! Much appreciated.

No problem. And yes, you can suppress the errors by using ISERROR:

Please Login or Register  to view this content.

Jason

If
HARRY JONES RR533 (26.02.07 TO 25.05.07)
is in A1,

=LEFT(A1,SEARCH(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),2)&CHAR(7))-1)

will return "HARRY JONES". It will never return an error value.

Originally Posted by mikerickson

If
HARRY JONES RR533 (26.02.07 TO 25.05.07)
is in A1,

=LEFT(A1,SEARCH(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),2)&CHAR(7))-1)

will return "HARRY JONES". It will never return an error value.

Cheer for that Mick. That works well too. Small ammendment though, if the surname has two words, it doesn't return the correct value. jason this is true for your formula as well. So for example if a person's name was John Von Dutch, it will only return John Von.

Is it possible to ammend the formula to return any characters before "_RR" (the underscore being the space before the RR)

Cheers guys!

Hi

Try

=IF(ISERROR(LEFT(A2,SEARCH(" RR",A2))),"",LEFT(A2,SEARCH(" RR",A2)))


rylo

Originally Posted by rylo

Hi

Try

=IF(ISERROR(LEFT(A2,SEARCH(" RR",A2))),"",LEFT(A2,SEARCH(" RR",A2)))


rylo

Thanks heaps RYLO. Just an addition, I needed to have -1 after the last string in the formula to get rid of the space. Otherwise that formula was perfect.

Thanks heaps!!!