Removing multiple instances of a character

[Jaryd]

Hello,

I have been a casual read of these forums for a while. This is my first post.

I was hoping someone could help me. I would prefer VBA code, but a forumla would be fine too

I pull reports and some of the information that comes in I need to clean up so filters can be used. I am trying to find a faster way to do this. It is currently the only part of the clean up I do manually. The best pattern I found is all the text I want to keep is after the last "."

There can be 1 through 3 "."s in each cell. So I could not get Text to Columns to work.

Any help would be great. Thanks

 

[kevatarvind]

what you need you want to remove all dot from each cell ?

or

can you post some data ( how data looks ) and what result you want ?

 

[Jaryd]

I need to remove all data before the last dot / Keep all data after the last dot

Correction from my first post. It is only ever 1 or 2 dots.


123. Keep this text1
132. Task: 20. Keep this text2
123. Task: 123. Keep this text1
12. Keep This Text1234d12
12. Task 50. Keep This Text3

 

[Akashwani]

Hi and welcome to MrExcel.

Do you mean something like this?....

Excel Workbook
	A	B	C
1	Data	Result
2	Ben.Red.Ten.Keep this	Keep this
3	Bob.Blue.Nine.Keep this text	Keep this text
4	Bill.Yellow.99999.Keep this too	Keep this too
5
Sheet1

The Search/Substitute part of the formula I got from here...
Last occurance of a character in a cell

I hope this helps.

Ak

 

[Jaryd]

That worked. Thank you so much. I did try finding this online first, but couldn't.

Now I can dissect that the formula to understand it. It is so much easier learning backwards like that.

 

[njimack]

Excel Workbook
	A	B
1	123. Keep this text1	Keep this text1
2	132. Task: 20. Keep this text2	Keep this text2
3	123. Task: 123. Keep this text1	Keep this text1
4	12. Keep This Text1234d12	Keep This Text1234d12
5	12. Task 50. Keep This Text3	Keep This Text3
Sheet1

 

[Akashwani]

Hi Jaryd,

I'm pleased it worked for you and thanks for the feeback.

If you are searching online for an Excel solution, prefix your search with Excel, eg....

Excel find last occurence of character in cell

I hope that helps for future reference.

Ak

 

[kevatarvind]

try below

Excel Workbook
	A	B
1	123. Keep this text1	Keep this text1
2	132. Task: 20. Keep this text2	Keep this text2
3	123. Task: 123. Keep this text1	Keep this text1
4	12. Keep This Text1234d12	Keep This Text1234d12
5	12. Task 50. Keep This Text3	Keep This Text3
Sheet3

 

[Scott Huish]

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",255)),255))

Excel Workbook
	A	B
1	123. Keep this text1	Keep this text1
2	132. Task: 20. Keep this text2	Keep this text2
3	123. Task: 123. Keep this text1	Keep this text1
4	12. Keep This Text1234d12	Keep This Text1234d12
5	12. Task 50. Keep This Text3	Keep This Text3
6
Sheet1

 

[Rick Rothstein]

If you are willing to use a formula, this is probably the simplest...

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",999)),999))

If, on the other hand, you want to physically change the values within the cells the text currently appears in, you do not need a formula nor VBA... select the column with your values, press CTRL+H to bring up the Replace dialog box, type *. (that is an asterisk followed by a dot) into the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the "Match entire cell contents" check box does not have a check mark in it, click the "Replace All" button

Finally, if you do want a VBA solution...

Columns("A").Replace "*.", "", xlPart