How to remove characters to the right of the last occurance of a special character?

G

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
Dear All,

I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.

I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.

If I can determine the position of the last occurance of the special character, I could use the LEFT function.

The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.

Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.

There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.

I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.

Thanks for your help,

GL
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Gary,

I used your InStrRev suggestion. InStrRev in Visual Basic for Applications (VBA) returns the position of a string within another string from the end of the string. It works great.

I tripped across another interesting VBA function, StrReverse. This function return a string in reverse. I don't know when I might need such a function now that I know InStrRev but is interesting none the less.

Thanks for the advice,

GL
 
Upvote 0
I noticed you said you preferred not to use VBA but I don't think there is an equivalent "Formula Operator" for InstrRev.

I actually tried it as a UDF. Here's what I came up with:

In a standard module:

Code: 
Public Function FindChar(sText As String)

FindChar = InStrRev(sText, "|")

End Function


Text In cell A1:

This | Is a | Test


Formula In Cell A2:

=LEFT(A1,(FindChar(A1))-1)

Gary
 
Upvote 0
The Non-VBA equivalent is a bit complicated:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">This | Is a | Test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">This | Is a</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=TRIM(<font color="Blue">LEFT(<font color="Red">A1,FIND(<font color="Green">"$",SUBSTITUTE(<font color="Purple">A1,"|","$",LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A1,"|","%%"</font>)</font>)-LEN(<font color="Teal">A1</font>)</font>)</font>)-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
General Ledger said:
Gary,

I used your InStrRev suggestion. InStrRev in Visual Basic for Applications (VBA) returns the position of a string within another string from the end of the string. It works great.

I tripped across another interesting VBA function, StrReverse. This function return a string in reverse. I don't know when I might need such a function now that I know InStrRev but is interesting none the less.

Thanks for the advice,

GL
Click to expand...

Thanks GL, StrReverse is awesome. Great lead.
 
Upvote 0
Gary McMaster said:
I noticed you said you preferred not to use VBA but I don't think there is an equivalent "Formula Operator" for InstrRev.

I actually tried it as a UDF. Here's what I came up with:

In a standard module:

Code: 
Public Function FindChar(sText As String)

FindChar = InStrRev(sText, "|")

End Function


Text In cell A1:

This | Is a | Test


Formula In Cell A2:

=LEFT(A1,(FindChar(A1))-1)

Gary
Click to expand...


Hi,

I have an issue similar to the one solved with your =LEFT(A1,(FindChar(A1))-1) formula. Looking for some help

I have a huge column of text in column C. Some of the text has the character : and some .

Any text after either character needs to be removed.

zxchfosor043.abgt.juisfd.com
sflkjlssf:sflkjlssf

What I need is a formula that will work on the entire column and give me the results

zxchfosor043
sflkjlssf

I have used the following formula to complete 50% of my problem
=IF(ISERROR(LEFT(C2,FIND(":",C2)-1)),C2,LEFT(C2,FIND(":",C2)-1))

Please help
 
Upvote 0
Welcome to MrExcel forum.

Try:
=LEFT(C2,MIN(FIND({":","."},C2&":."))-1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Solved
vba remove consecutive characters from the Right
Replies
2
Views
78
gtd526
gtd526
B
  • Question
VBA macro for removing entire alphabet some special characters
Replies
1
Views
246
BobMaccaroni
B
M
  • Question
[Excel->Word] Filling in Word document adds unwanted special characters
Replies
3
Views
258
NdNoviceHlp
NdNoviceHlp
B
  • Solved
Function to Remove Special Characters
Replies
3
Views
620
bcarey1
B
N
  • Question
Dismissing special characters in a text string
2
Replies
17
Views
614
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,659
Messages
6,120,781
Members
448,992
Latest member
prabhuk279

Share this page

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
Back
Top