Read column right-to-left

[tvisgod]

I'm hoping for help crafting a function to apply to all the cells (containing entries of various lengths) in a column.
The function should "read" the characters in a cell Right-to-Left, searching for a particular sequence. In my case the sequence is kay-space-semicolon. In other words...
; K (left to right)
or
K ; (if read right to left)
The function would eliminate everything left of the K (including the semicolon and the space) when this sequence is found. If the sequence is not found, the cell remains as normal.
Example: as;lk djf 83; K;lasd K would return K;lasd K

Any help would be appreciated. Thank you.

 

[Trouttrap2]

you can try this
=IFERROR(MID(A1,FIND("K;",A1),100),A1)

 

[JoeMo]

Assuming the K-space-semicolon appears only once in a string:

Excel Workbook
	A	B
1	as;lk djf 83; K;lasd K	K;lasd K
Sheet4

 

[V_Malkoti]

Or

=IFERROR(RIGHT(A1,LEN(A1)-FIND("K;",A1,1)+1), "")

in case you may have more than 100 characters to the right of "K;"

 

[tvisgod]

Or

=IFERROR(RIGHT(A1,LEN(A1)-FIND("K;",A1,1)+1), "")

in case you may have more than 100 characters to the right of "K;"

Thank you. I will not have more than 100 characters to the right of "K," but I may have more than one "; K" sequence in a cell. In those instances, I only wanted to retain the right-most portion of the cell, and was fine eliminating the earlier (left-most) "; K" sequences. That is why I requested a right-to-left function.

Thank you for your help.

 

[Rick Rothstein]

Assuming the K-space-semicolon appears only once in a string:

=MID(A1,FIND("; K",A1)+2,LEN(A1))

If it could appear more than once, then this formula should work...

="K"&TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"; K",REPT(" ",200)),200),200))

Note: If your text could be longer than 200 characters, change each of the 200s in my formula to a number larger than the longest possible text value.

 

[V_Malkoti]

If K; appears more than once:


=TRIM(RIGHT(SUBSTITUTE(A1, "K;", REPT(" ", 255) & "K;"), 255))

 

[tvisgod]

If it could appear more than once, then this formula should work...

="K"&TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"; K",REPT(" ",200)),200),200))

Note: If your text could be longer than 200 characters, change each of the 200s in my formula to a number larger than the longest possible text value.

This worked great, with one exception...

When the cell includes NO semicolon-space-K sequence, the cell's contents are reproduced with an extra "K" at the beginning.

Example:

KF1524 becomes KKF1524

<colgroup><col width="384"></colgroup><tbody>
</tbody>

Any ideas?

 

[stan423321]

=IF(ISERROR(FIND("; K", A1)); A1; the current formula)

Maybe something like this?

 

[Rick Rothstein]

This worked great, with one exception...

When the cell includes NO semicolon-space-K sequence, the cell's contents are reproduced with an extra "K" at the beginning.

Example:

KF1524 becomes KKF1524

<colgroup><col width="384"></colgroup><tbody>
</tbody>

Any ideas?

I think this will handle that situation...

=IF(COUNTIF(A1,"*; K*"),"K"&TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"; K",REPT(" ",200)),200),200)),A1)