using subtraction to take part of string and remove piece of it

Hey all,

I have formula:

Please Login or Register  to view this content.

This takes this:
KNUD J & MARIA L HOSTRUP

And returns this:
HOSTRUP,HOSTRUP

when I want it to return this:
HOSTRUP,MARIA L

Without overcomplicating, does anyone know how I can take length after the ampersand and subtract the last word from that length?

Is there a reason why HOSTRUP just happens to be the same number of characters as KNUD J ?
You seem to use it for your formula, so I'm assuming that only 7 characters are used in the last name?

If that's the case you can use the below code:

Use the Find("&",A48)+1 to find where the "&" is, and add 1 to compensate for the Space after the "&" symbol.

We can then use the Mid formula to return cell A48 starting from 1 character after the "&" symbol. This conveniently starts at Maria.

I use the formula you provide which returns HOSTRUP which is conveniently 7 characters (thus why I asked if the last name would only be 7 characters), and i embed that find in another Find formula. In other words, the new Find is finding HOSTRUP and telling me when that starts.

This code is: FIND(RIGHT(A48,FIND("&",A48,1)-1),A48) and happens to be the 18th character.

I then determine the Length of the rest of A48 starting from Maria, subtract the length of HOSTROP (which is 7, and if its not, then this formula will probably not work across cells)

and the result is that the Mid formula, starts after "&" and ends right before H.
Lemme know if this works for you.


Entire code:

Please Login or Register  to view this content.

take length after the ampersand and subtract the last word from that length

So the key is to find the last space after the ampersand. If there are always a constant number, then it's fairly straightforward,
Find the ampersand
> Find the first space after the ampersand (excl the one immediately following it
> Find the first space after that space
(sorry, hit 'post' - will finish this off in next post!)

Assuming there's always a middle initial...
If text is in A1:
a) =FIND("&",A1) finds the ampersand
b) Avoiding the space after it, you can find the next space with
=FIND(" ",A1,FIND("&",A1)+2)
c) Then find the last space with
=FIND(" ",A1,FIND(" ",A1,FIND("&",A1)+2)+1)

d) Once you have this, you can take the right-hand N-(c) characters of the text, where N is the text length LEN(A1) and (c) is step (c) above, using the RIGHT() function

Sorry, revised step (d) - the above selects the last word. You want to remove it from the text after the ampersand.

d) Once you have this, you can calculate the right-hand N-(c) characters of the text, where N is the text length LEN(A1) and (c) is step (c) above. We'll need this below.

e) Text after the ampersand is
=RIGHT(A1,LEN(A1)- (a) ), where (a) is step a) above.

f) Take the left-hand M characters, where M is the length after the ampersand minus the length of the last word
=LEFT( (e) - (d) )

Will put this in a spreadsheet and attach shortly(!).

You know when you sit down to do something, and something occurs to you...?
=MID(A1,FIND("&",A1)+1,FIND(" ",A1,FIND(" ",A1,FIND("&",A1)+2)+1)-FIND("&",A1))

The MID() function extracts text from the middle of other text. You tell it where to start and how many characters to find.
Start at the ampersand + 1 (or +2 if it's always followed by a space)
FIND("&",A1)+1
Number of characters is location of second space minus location of ampersand
FIND(" ",A1,FIND(" ",A1,FIND("&",A1)+2)+1)-FIND("&",A1)
..possibly -1 if you're excluding that space after the ampersand.

Further enhancement. If there's sometimes a middle initial, but sometimes not, use the IFERROR() function in the middle, so that if there's no second space after the & you only look for the first.

I've also tidied up the +1s to +2s to get rid of leading and trailing spaces.

Attached.

Thanks that kind of what I was asking for. Thanks for explanations.