Excel Search and Replace help needed

[Blueness]

Dear sir/madam,

I have an Excel sheet with a column containing numbers. Example 5,10,25 I want to replace the number 5 with the text earring. Unfortunately with the function Search adn Replace Excel also changes the 25 into 2Earring. I tried to input the 5 as "5" or '5' to have an exact match but this does not help. Also ticking the box exaxt matches only does not help. Who knows a solution for this?

Thank you in advance for your help and reply.

Best Regards,

Michael

[DBY]

Hello
When using Find & Replace, check: 'Match entire cell contents'. This should then replace all single digit 5's with 'earrings'.

Hope this helps.
DBY

[Blueness]

Hi DBY,

Thank you for your reply. Unfortunately Excel gives the message: Did not found matching data (translated from Dutch). The sheet is not protected.
I think with this option Excel wants the exact data in the cell as I input. For example is the cell data is: 1,5,20,25 it would want 1,5,20,25 and not only the 5.

Any other ideas? My sheet has 12.000 rows that's why I want to automate it

Best Regards,

Michael

[Ace_XL]

You'd probably want to use

=SUBSTITUTE(A1,",5,",",earring,")

[DBY]

Hello
Sorry I misunderstood the question. I thought each cell contained a single number:1; 5; 20; 25 etc. I didn't realize you were referring to a string of numbers in each cell. If there are no spaces between the commas and numbers try:

Uncheck 'Match entire cell contents, and enter the Find criteria as: ,5

and the Replace criteria as: ,earring

Else try Ace_Xl's excellent suggestion.

DBY

[Blueness]

Hi all,

Thank you for you replies. @DBY no problem at all, I'm thankful of all the help I get.
If I try the solution provided by ACE_XL, Excel says there is an error in the formula. When I follow the guidelines Excel provides then the cell says: #VALUE!

DBY his solution works. I did not think of adding the comma.

Best Regards,

Michael

[Cutter]

@ Blueness

Based on your last post I will mark your thread as SOLVED. Please remember to do that yourself for your future threads.

Thanks.

[Blueness]

Dear Cutter,

Thank you for the notification, I will be aware of that in the future.

Best Regards,

Michael

[Blueness]

Hi All,

How do I mark this topic unsolved? Because in my excel sheet I have data like 5, 25, If I search and replace 5, with Earrings the 25, will be changed into 2Earrings. To provide a good example I have attached the file.

Thank you in advance for the help.

Best Regards,

Michael

[Cutter]

I'll do that for you now.

[Cutter]

Will there just be the 2 possibilities for appearance of 5, - those being 5, and 25, ?
If so you can do a find replace on 25, first - changing it to "aa," or whatever.
Then do the find replace on the 5,
Then go back and do find replace on aa, to replace it with 25,

[Blueness]

Hi Cutter,

Thank you for your help. In the overview I do not see data like 55. The highest number is 43. If I look at the number 3 for example and following your suggestion I first need to clear out numbers like 43,30, 13 etc.
I will post the overview here. I only want to use the top categories in the Excel sheet, I want to delete the sub categorie numbers in the Excel sheet.

Category ID
Oorbellen 5
-Met hanger(s) 10
 1 deel 23
 2 delig 24
-Clip 11
 1 deel 25
 2 delig 26
-Steker 12
 1 deel 27
 2 delig 28
-Creool 13
Ringen 6
-Niet aanpasbaar 14
-Aanpasbaar 15
Broches 7
Armbanden 8
-Gesloten Slavenarmband 16
-Sluiting 18
-Open Slavenarmband 41
-Scharnierend 42
-Elastisch 43
Kettingen 9
-Kort 19
 Zonder hanger 29
 Met hanger 30
-Lang 20
 Zonder hanger 31
 Met hanger 32
-Choker 21
 Zonder hanger 33
 Met hanger 34
Piercings (disabled) 36
Sjaals 37
Overige 39

Top category
Category ID
Oorbellen 5
Ringen 6
Broches 7
Armbanden 8
Kettingen 9
Sjaals 37
Overige 39

I hope I am a little clear.

Best Regards,

Michael

[vlady]

i think 5 is found at the start of the string????

=IF(LEFT(A1,1)=5,REPLACE(A1,1,1," earrings"),A1)

[Blueness]

Thank you for the formula, but Excel returns an error. I think I can realise the solution with first replacing the numbers like 25 with nothing and then apply the top level category changes. THank you for all the lp and quick responses.

Best Regards,

Michael