Remove unwanted text and numbers from lists (whilst keeping one value/word)

[joedoherty]

Hi

I would really appreciate some help. I want to know if there away of removing all but "one word" from a column of data. In the attached example, I want to remove all the value except for the word "TESCO" from the cells in the column that have the word "TESCO" in them

Is there a function for this?

SEE ATTACHED

[Glenn Kennedy]

Yes;


=IF(ISNUMBER(SEARCH("TESCO",A2)),"Tesco",A2)

copied down.

[joedoherty]

Hi Glenn

Thanks for the reply, but I wanted to remove everything else , so that all that is left in the cell is "TESCO".... I think your formula removes "TESCO".... which is the opposite of what I am after. It's driving me round the bend!

[AliGW]

Try this tweak:

=IF(ISNUMBER(SEARCH("TESCO",A2)),"Tesco","")

[joedoherty]

Hi AliGW

Yes, that works.... thanks Ali 😊👌👍

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[torachan]

Small macro on green button

[joedoherty]

Hi Ali.... I did what you said re. the Star..... However, I can't see the link "Thread Tools".... can you tell me where that is? (sorry to be a nuisance)

[joedoherty]

Brilliant torachan!.... how did you do that? (if you don't mind me asking.

[AliGW]

Hi Ali.... I did what you said re. the Star..... However, I can't see the link "Thread Tools".... can you tell me where that is? (sorry to be a nuisance)

To be clear, I did not TELL you to reward rep - I just said it was possible should you wish to do so, but thanks.

The thread tools drop-down is top right (just above the opening post), or you could edit the opening post and go advanced to change the drop-down selector next to the title box to solved.

[joedoherty]

👍 understood

[AliGW]

Great - so can you mark it as solved, then?

[joedoherty]

No not yet, as there is another member I am communicating with, he/she sent me a solution using a macro... so I am waiting on a reply

[AliGW]

Ah, OK - Torachan isn't currently online. I'm sure he'll give you the help you've asked for soon.

[joedoherty]

👍 Yes, I am waiting on a reply from Torachan

[torachan]

right mouse click on sheet1 tab - small menu appears - select 'view code' - this code is in the 'sheet module' triggered by the 'command' button.
alternatively the code could be placed in a 'standard module' and run as a macro from the macro reader.
the code is almost 'english' to understand - find the last row in column 'A' - then loop through rows 2 to ALastRow - assign the value in each cell individually to variable 'a' as a string - use the VBA function 'Instr' - to see if the string "TESCO" is in string variable 'a' - if true then overwrite the cell value with "TESCO"

Please Login or Register  to view this content.

[joedoherty]

That's great help, thanks a lot torachan.... much appreciated. Great help from you both (yourself and AliGW)