Substitute word from one pick list with a word from another picklist

**Miles_G** · 10-31-2024, 06:15 PM

Hi,
I have tried to simplify a list by splitting into two picklists where I want to substitute the word Customer for the actual customer name, rather than having a 400 deep picklist, I can have 40.
E.g. Customer Rocks is selected in A1, the name of the customer ("Harry") is in B1. In C1 you substitute the word customer with the customer name in B1. C1 would therefore be "Harry Rocks".

Within my picklist I also have words in front of "Customer", and therefore I can't just replace by place value.

A1= Customer Rocks
A2= New Customer submission

B1 = Harry
B2 = Harry

C1 = Harry Rocks
C2 = New Harry submission

Is there a way to do this?

**Estevaoba** · 10-31-2024, 07:27 PM

Welcome to the forum.

Try in C1 and drag down:

Formula:

=IF(LEFT(A1,3)="New","New"&" "&XLOOKUP(A1,$M$1:$M$4,$N$1:$N$4,"Not found")&" "&SUBSTITUTE(SUBSTITUTE(A1,"Customer ",""),"New",""),IF(LEFT(A1,4)="Last","Last"&" "&XLOOKUP(A1,$M$1:$M$4,$N$1:$N$4,"Not found")&" "&SUBSTITUTE(SUBSTITUTE(A1,"Customer ",""),"Last",""),XLOOKUP(A1,$M$1:$M$4,$N$1:$N$4,"Not found")&" "&SUBSTITUTE(A1,"Customer ","")))

Good luck!

**Miles_G** · 10-31-2024, 07:46 PM

Thank you that worked, I can iterate from there if there are any different words in front.

**Estevaoba** · 10-31-2024, 07:51 PM

You’re welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day.

**Miles_G** · 10-31-2024, 08:06 PM

Actually, that only works if you alter the pick list in column A. If I alter any name in B, the customer name doesn't change. Ah well, I'll see what I can fathom out.

**mojirhi** · 10-31-2024, 08:14 PM

Try this formula.

Formula:

C1 =SUBSTITUTE( A1, "Customer", FILTER($N$1:$N$4, COUNTIF(A1, "*"&$M$1:$M$4&"*")>0, "??") )

**Miles_G** · 11-01-2024, 11:54 AM

Unfortunately, that has the same issue, that changing anything in column B doesn't update Column C

**Miles_G** · 11-01-2024, 11:57 AM

C1 =SUBSTITUTE( A1, "Customer", B1) seems to work

Substitute word from one pick list with a word from another picklist

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Re: Substitute word from one pick list with a word from another picklist

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to search and match a word from colum A to a list of word given in another column

[SOLVED] Substitute word by list

Include random word from the word list with a phrase as suffix or prefix

Search a text string to see if it contains a word from a defined list, return that word

[SOLVED] How can I replace a word from a sentence in a cell & substitute word from another cell

Creating an Excel picklist to import into word?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions