Count the number of numbers to the right of the "X"

[gsdanger]

Good Day all you Excel gurus (Wish I was one of you...lol)

I have a string containing mostly number, but occasionally, there is are X's included.
I would like to know how can I determine how many numbers occur, to the right of the first "X" in the string, starting from the right of the string.

A string Example: 21X50X16880X1074
I want to determine how many numbers occur to the right of the first occurrence of the "X" in the string, starting from the right side.

In the example, the answer is 4, however, I need to automate this count.

Please assist.

Kind Regards.....gsdanger...

 

[Aladin Akyurek]

Try...

=LEN(REPLACE(A2,1,FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))),""))

 

[gsdanger]

Good Day Aladin Akyurek,
Thank you for your prompt and positive reply to my problem.
I copied your formula into my spreadsheet, and it works beautifully....Thank you, Thank you, Thank you.
I will try and understand what the code means, when I have a bit more free time.

Thanks heaps....You are a legend......Kind Regards....gsdanger.

 

[Rick Rothstein]

I have a string containing mostly number, but occasionally, there is are X's included.
I would like to know how can I determine how many numbers occur, to the right of the first "X" in the string, starting from the right of the string.

A string Example: 21X50X16880X1074

Assuming your text is never longer than 99 characters (if it is, then make the two 99's larger), then I think this formula might also work for you...

=LEN(TRIM(RIGHT(SUBSTITUTE(UPPER(A1),"X",REPT(" ",99)),99)))

Note: If your text has no X's in it, my formula returns the length of the text. If that is a possibility and you would rather it return 0 instead, then use this formula...

=IF(COUNTIF(A1,"*X*"),LEN(TRIM(RIGHT(SUBSTITUTE(UPPER(A1),"X",REPT(" ",99)),99))),0)

Also note that my formulas do not count trailing spaces (I notice that Aladin's formula does).

 

[Aladin Akyurek]

Good Day Aladin Akyurek,
Thank you for your prompt and positive reply to my problem.
I copied your formula into my spreadsheet, and it works beautifully....Thank you, Thank you, Thank you.
I will try and understand what the code means, when I have a bit more free time.

Thanks heaps....You are a legend......Kind Regards....gsdanger.

You are welcome.

In

=LEN(REPLACE(A2,1,FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))),""))

the

LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")) determines how many X instances the target counts.

The

SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))​

bit substitutes "|" for the last instance of "X" in the target.

The

FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X",""))))​

bit determines the position of "|", that is, the position of the last "X".

The

REPLACE(A2,1,FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))),"")​

bit replaces the target from its first char till its last instance of "X", leaving just the part after the last "X".

The surrounding LEN delivers the length of the last part from the previous step, the desired outcome.

Hope this helps.


​

_{^{<strike>
</strike>}}​

 

[gsdanger]

Good Day Aladin Akyurek and Rick Rothstein,
Again, thanks for your responses, and Thanks Aladin for attempting to educate me on the formula breakdown...I appreciate it.
Rick, I have switched to your solution (the first one), as I forgot to mention that sometimes there may not be any Xs in the cell, so your formula will accommodate this situation.
Once again, thanks guys, and have a great Xmas and a great new year.....

Kind Regards.....gsdanger

 

[Aladin Akyurek]

Good Day Aladin Akyurek and Rick Rothstein,
Again, thanks for your responses, and Thanks Aladin for attempting to educate me on the formula breakdown...I appreciate it.
Rick, I have switched to your solution (the first one), as I forgot to mention that sometimes there may not be any Xs in the cell, so your formula will accommodate this situation.
Once again, thanks guys, and have a great Xmas and a great new year.....

Kind Regards.....gsdanger

Just for the record, the lengthy formula is also subject to the same modification...

=

IF(COUNTIF(A2,"*X*"),​

LEN(REPLACE(A2,1,FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))),"")),A2)

=IF(ISNUMBER(SEARCH("X",A2)),

LEN(REPLACE(A2,1,FIND("|",SUBSTITUTE(UPPER(A2),"X","|",LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")))),"")),A2)​

​

<strike></strike>