Need to either Find / Replace / Insert text into a string in a column of text -

Question

Need to either Find / Replace / Insert text into a string in a column of text - Please assist.

Hi,

I am working on an inventory upload sheet. It draws data from a database and I am using excel to manipulate, format and massage the data before uploading it to a website.

One of the issues with the original text is it is very truncated. I need to flesh it out.

I need to be able to find brand names and other descriptive text scattered in the individual strings of text in a single column and replace them with other text.

The Example below is very similar for all text strings in the column:

Original Text String: H3 under lying formula ='Entry Form'!B2

Desired result: H3 under lying formula ='Entry Form'!B2

NOTE: The cell is pulling from another sheet and the original text is the result of a concencate formula.

I have tried to use the "REPLACE" function but get "#VALUE" in the cell.

The end "for E39 530i 02" I can correct with the concencrate function but the front I need help with.

I need to delete or move the "BMW"

I also need to add "Genuine BMW OEM pt# " to the beginning

Is there an easy way to do this?

61368384505 BMW WIPER RELAY BROWN E39 530i 02

Explanation / Answer

Given in A1:A...

In B1 enter:

=TRIM("Genuine " & LEFT(MID(A1,FIND(" ",A1)+1,99),FIND(" ",MID(A1,FIND(" ",A1)+1,99))-1) & " OEM pt# " & SUBSTITUTE(A1,LEFT(MID(A1,FIND(" ",A1)+1,99),FIND(" ",MID(A1,FIND(" ",A1)+1,99))-1),""))

This is an ARRAY formula, enter it with CONTROL+SHIFT+ENTER

and drag down.

Gives:

Note that Rolls Royce has become Rolls.

By lack of info, I don't see how to insert "for" i nthe string.

61368384505 BMW WIPER RELAY BROWN E39 530i 02 61368384506 Mercedes WIPER RELAY BROWN C220 61368384507 Fiat WIPER RELAY BROWN E39 530i 02 61368384508 Toyota WIPER RELAY BROWN E39 530i 02 61368384508 Chevrolet WIPER RELAY BROWN E39 530i 02 61368384508 Pontiac WIPER RELAY BROWN E39 530i 02 61368384508 Rolls Royce WIPER RELAY BROWN E39 530i 02 61368384508 Bentley WIPER RELAY BROWN E39 530i 02 61368384508 Porsche WIPER RELAY BROWN E39 530i 02 61368384508 Peugeot WIPER RELAY BROWN E39 530i 02 61368384508 Renault WIPER RELAY BROWN E39 530i 02 61368384508 Lexus WIPER RELAY BROWN E39 530i 02 61368384508 Ferrari WIPER RELAY BROWN E39 530i 02 61368384508 Maserati WIPER RELAY BROWN E39 530i 02 61368384508 Skoda WIPER RELAY BROWN E39 530i 02 61368384508 Zastava WIPER RELAY BROWN E39 530i 02

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.