CODEWITHSUNDEEP
vbaexcelexcel-formula
user3663068
user3663068

Reputation: 25

How to scan a column for a certain word and when triggered, remove the first 4 characters

Hey everyone so here is my setup

enter image description here

My issue is regarding the "C" column and how to remove the "2nd|" and "3rd|" portions so it ONLY shows "Mikes Auto Shop" "Carls Auto Repair" etc

Like this

http://imgur.com/a/ddpYq

Upvotes: 1

Views: 83

Answers (3)

Michael
Michael

Reputation: 4858

If there is only sometimes a prefix or if it can be larger than 9, you can use:

=RIGHT(C2,LEN(C2)-IFERROR(FIND("|",C2),0))

Upvotes: 1

Brian Mc Donagh
Brian Mc Donagh

Reputation: 11

https://www.mrexcel.com/forum/excel-questions/375502-remove-first-letter-each-cell-column.html

on the forum here they mention that you can use =RIGHT(A1,LEN(A1)-1), for you it would look more like =RIGHT(C1,LEN(C1)-1) and you would place it in the D column and then just grap the bottom right hand corner and drag it down the will give you all the values you need in the D column, hope this helps

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

Just a formula in D2 will do the trick

=RIGHT(C2,LEN(C2)-4)

Upvotes: 1

Related Questions