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.

 

[Rick Rothstein]

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

@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

@
When I set the formula up this way, I can enter it normally and it works fine...

=MID(A2,FIND(" ",A2,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),0)))+1,99)

One note though... when I copy/pasted the example the OP posted, I found that the character after the last digit was not a normal space (ASCII 32), rather, it was a non-breaking space (ASCII 160) like you can get when you copy text from a web page. So, if the character after the last digit is always a non-breaking space, then this formula can be used...

=MID(A2,FIND(CHAR(160),A2,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),0)))+1,99)

If, on the other hand, that character can be either a normal space or a non-breaking space, then this formula can be used...

=MID(A2,FIND(" ",SUBSTITUTE(A2,CHAR(160)," "),MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),0)))+1,99)

 

[araris]

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

@Special-K99

Sorry, didn't think.


You can find a decent size sample here: https://drive.google.com/file/d/19ko8XWqdU7cKOEfZkOccxtB3Qc6mW-kS/view?download=1

 

[Special-K99]

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

Ok, but that file does not reflect the image you put up earlier, filled with #VALUE errors.
You may get #VALUE errors if you don't enter the formula as an array.

I entered the formula as an array on the spreadsheet you just referenced and I got no #VALUE errors and the output looked ok.

 

[araris]

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

You're correct, I copied the stuff in a clean file and there were no #VALUE errors in the new one. I'm guessing there were bad chars inside the original. Thanks for the help.

 

[Rick Rothstein]

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

You're correct, I copied the stuff in a clean file and there were no #VALUE errors in the new one. I'm guessing there were bad chars inside the original. Thanks for the help.

Just out of curiosity, did you see what I posted in Message #11 yet?

 

[araris]

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

Just out of curiosity, did you see what I posted in Message #11 yet?

Yes I did, I cleaned the data based on that. Thanks for your help.