Find a space from the right

  • Thread starter Thread starter Basz
  • Start date Start date
B

Basz

How can I search a text string from right to left to find the last spac
in the string?

I need to trim a database containing names to the last word in it, bu
can't fin out how to go about.

Thanks,
Bas
 
Hi Basz.,

This should do it

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
 
Bob Phillips wrote...
This should do it

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
Click to expand...
...

Shorter alternatives

=LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)=" "),
ROW(INDIRECT("1:1024")))

[*array* formula]
=MAX(IF(MID(A1,ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))))

Since the ROW(INDIRECT("1:1024")) term is generally useful for a hos
of text parsing tasks, it makes sense to give it a defiend name, lik
seq, in which case these formulas become

=LOOKUP(2,1/(MID(A1,seq,1)=" "),seq)

[*array* formula]
=MAX(IF(MID(A1,seq,1)=" ",seq)

The FIND formula can't be shortened significantly using defined names
 
Thanks, the first formula does the trick. Would you care to explain ho
it works?

You really helped me out, thanks again!
[...]
Shorter alternatives

=LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)=" "),
ROW(INDIRECT("1:1024")))

[*array* formula]
=MAX(IF(MID(A1,ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))))

Since the ROW(INDIRECT("1:1024")) term is generally useful for a hos
of text parsing tasks, it makes sense to give it a defiend name, lik
seq, in which case these formulas become

=LOOKUP(2,1/(MID(A1,seq,1)=" "),seq)

[*array* formula]
=MAX(IF(MID(A1,seq,1)=" ",seq)

The FIND formula can't be shortened significantly using define
names. [/B
Click to expand...
 
Basz > said:
. . . Would you care to explain how it works? ....
hgrove wrote: ....
Click to expand...
....

LOOKUP as well as VLOOKUP and MATCH searching for approximate matches (4th
or 3rd argument FALSE or 0, respectively) seem to begin by trying to bracket
the lookup value, and so far through Excel 2003 they predictably stop at the
final numeric value in the range if the lookup value is greater than any in
the range. Further, and *vitally*, LOOKUP and these other functions ignore
error values (or any other nonnumeric values) in the lookup range.

In this formula, MID(..) in LOOKUP's 2nd argument returns an array of all
individual characters in the value of A1 as separate strings followed by ""
strings. For example, MID("foo",{1;2;3;4},1) returns {"f";"o";"o";""}. These
are each compared to a space character, " ", returning an array of TRUE or
FALSE values. The formula then divides 1 by each of these values. The
arithmetic operation converts the TRUEs to 1s and the FALSEs to 0s, so the
result is an array of 1s and #DIV/0! errors.

At this point, none of the values in LOOKUP's 2nd argument array are greater
than 1, so looking for 2 in this range will locate the last 1, which
corresponds to the last space in A1. It then returns the corresponding value
in its 3rd argument, which is just a sequence of integers.
 
For an approximate match, I think you meant 3rd or 4th argument omitted, TRUE,
or 1, didn't you?
 
Myrna Larson said:
For an approximate match, I think you meant 3rd or 4th argument
omitted, TRUE, or 1, didn't you?
Click to expand...
....

Yup. Screw-up. Thanks for catching it.
 
Back
Top