Select the text after the space after the last digit in the cell?

[araris]

I have to solve the question above to avoid manually splitting 1000 lines. I'm not quite sure if the part about the last digit is possible however. Can that be done somehow? I think I can manage the rest if I knew how to get the index of the last digit in a cell.

Some address in some city #50 Boris Johnson
Some other address in some city #20A Donald Trump

The idea here is to catch the names at the end, however they are not structured in any way, maybe one name or more may have additional info.
Thanks for any help you can offer.

 

[Joe4]

Re: How do I select the text after the space after the last digit in the cell?

Welcome to the Board!

I think it would be very helpful if you could post a bunch of actual (complete) examples, trying to show all the different possible structures that you may encounter (one name, multiple names, etc).
I think we need to see how all these different records really look to see if we can come up with something that might work for you.

 

[Special-K99]

Re: How do I select the text after the space after the last digit in the cell?

This should work on both pieces of data, as long as there is no numeric digit after the name, e.g. George Hamilton 4th, Louis 14th etc

=MID(A1,FIND(" ",A1,MAX(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A1),-E199))+1)+1,1000)

 

[njimack]

Re: How do I select the text after the space after the last digit in the cell?

So are you trying to return everything after the first space? If so, try:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[Rick Rothstein]

Re: How do I select the text after the space after the last digit in the cell?

Some address in some city #50 Boris Johnson
Some other address in some city [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2 0A[/URL] Donald Trump

Will that last "number" always start with a # sign like your two examples show? If so, this formula should do what you want...

=MID(A2,FIND(" ",A2,FIND("#",A2))+1,99)

If not, please respond to Joe's question in Message #2 .

 

[araris]

Re: How do I select the text after the space after the last digit in the cell?

This should work on both pieces of data, as long as there is no numeric digit after the name, e.g. George Hamilton 4th, Louis 14th etc

=MID(A1,FIND(" ",A1,MAX(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A1),-E199))+1)+1,1000)

Hi,

I can confirm that there will be no digits in the later part of the text I'm trying to catch.

This worked on some of them but not all, here are some examples where it didn't work:

[TABLE="width: 1014"]
<tbody>[TR]
[TD="width: 1014"]example1:
- for this "Стара Загора улица Господин Михайловски, № 30, ет.1 Антон Георгиев Герджиков и Тодор Кирилов Урумов-ЗАЕДНО и ПООТДЕЛНО"
- returned "ет.1 Антон Георгиев Герджиков и Тодор Кирилов Урумов-ЗАЕДНО и ПООТДЕЛНО"

which captures the the floor of the address and instead it should start after it as it is the last digit on the line.

[/TD]
[/TR]
</tbody>[/TABLE]
example 2 (worked):
- for this "Стара Загора улица Христо Белчев № 100А Владимир Танев Вълев – управител"
- returned "Владимир Танев Вълев – управител" which is the optimal answer even the letter of the building entrance didn't confuse it.

example 3 (didn't work):
- for this "София р-н „Илинден”, ул. „Кукуш” № 7 Ангел Андонов Ирибозов и Руди Христов Баков - представители"
- it didn't return an answer at all, just #Value.

=MID(A1,FIND(" ",A1,MAX(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A1),-E199))+1)+1,1000)

Also what is the point of the bolder part above I'm not clear on that.

 

[araris]

Re: How do I select the text after the space after the last digit in the cell?

Will that last "number" always start with a # sign like your two examples show? If so, this formula should do what you want...

=MID(A2,FIND(" ",A2,FIND("#",A2))+1,99)

If not, please respond to Joe's question in Message #2 .

No, the number can be preceded by several different characters like space, dot, numero and others.

 

[Special-K99]

Re: How do I select the text after the space after the last digit in the cell?

@araris:

Oops my mistake, I suppled an array formula, you have to enter it differently.

=MID(A1,FIND(" ",A1,MAX(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A1),-E199))+1)+1,1000)
Array formula, use Ctrl-Shift-Enter

As the formula is an array formula you want the highest position MAX() of the digits 0-9.
MAX will return the position of one of the digits 0 - 9.
But if a digit is not found it will return an error.
By placing the search in an IFERROR the value -E199 will be when a digit cannot be found , -E199 is a very low number.
So IFERROR(SEARCH(...))) will ALWAYS produce a number either the highest position of a digit it has found or -E199 if it cant find a digit.
Since MAX finds the highest number all -E199s will get ignored thus returning the highest position of the digits 0-9 that exist in the string.

 

[araris]

Re: How do I select the text after the space after the last digit in the cell?

@araris:

Oops my mistake, I suppled an array formula, you have to enter it differently.

=MID(A1,FIND(" ",A1,MAX(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A1),-E199))+1)+1,1000)
Array formula, use Ctrl-Shift-Enter

As the formula is an array formula you want the highest position MAX() of the digits 0-9.
MAX will return the position of one of the digits 0 - 9.
But if a digit is not found it will return an error.
By placing the search in an IFERROR the value -E199 will be when a digit cannot be found , -E199 is a very low number.
So IFERROR(SEARCH(...))) will ALWAYS produce a number either the highest position of a digit it has found or -E199 if it cant find a digit.
Since MAX finds the highest number all -E199s will get ignored thus returning the highest position of the digits 0-9 that exist in the string.

So I'm trying to break down your formula so I can use similar thing later on:

It seems the rows on which the whole formula fails are the rows on which the search fails, but I can't figure out why would the search fail on those rows.

Here is an example where the formula fails, the part about search alone returns #VALUE . Any idea why?

https://imgur.com/avAKzLE

 

[Special-K99]

Re: How do I select the text after the space after the last digit in the cell?

Can you post a link to the file, I can't do much with an image.