Read column right-to-left

tvisgod

New Member
Joined
Jul 20, 2011
Messages
18
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming the K-space-semicolon appears only once in a string:
Excel Workbook
AB
1as;lk djf 83; K;lasd KK;lasd K
Sheet4
 

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

New Member
Joined
Jul 20, 2011
Messages
18

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 10, 2015
Messages
898

ADVERTISEMENT

If K; appears more than once:


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

tvisgod

New Member
Joined
Jul 20, 2011
Messages
18
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top