How to replace the middle of an Excel cell?

Question

I have a list of inventory for some of my products in Excel. I need to replace the middle of the cell with certain data.

Let's say each cell has data like:

Ronald RFD2322 Green 34 x 55 Large

But each cell is different.

One may be like the above, and the one below may be

Ronald RFD8827 Blue 21 x 25 Small

I need to replace the middle section, that looks like a model number (RFD2322), with a name, but some names are different. I need the cells to look like

Ronald Jimmy Green 34 x 55 Large
Ronald Sarah Blue 21 x 25 Small

If no one has an answer for that, how to at least replace the middle of the cell with a blank space so I can go back and fix it manually?

Will *all* of your products start with `RFD`? Or `RF`, or ...? How can we know that it's a product ID and not some text, a person's name, etc.? (will it always have 7 characters? will it always start with R, and be 7 characters, etc. We just need a way to detect the pattern, then it should be pretty simple.) — BruceWayne, Sep 16 '16 at 15:25
Along with above, do you have a table somewhere that equates what should be replaced with what should replace it? — Scott Craner, Sep 16 '16 at 15:28
Yes, they all start with RFD, and have 4 digits after that, so each are 7 chararacters. Also, yes, I have the names in order that need to be put into the space where the model # is. — nick-w-nick, Sep 16 '16 at 15:31

score 1 · Accepted Answer · answered Sep 16 '16 at 15:32

1

This formula will return the Ronald Green 34 x 55 Large assuming data is in cell A1

=LEFT(a1,FIND(" ",a1)-1)&MID(a1,FIND(" ",a1,FIND(" ",a1)+1),99)

Looking at your problem, it would more sense if the data to be substituted was in a column. E.g. if Jimmy is in B1

=LEFT(a1,FIND(" ",a1)-1)&B1&MID(a1,FIND(" ",a1,FIND(" ",a1)+1),99)

NB 99 is a random number, chosen as don't expect you'll have more than that many characters. It's silly as it's optional for LEFT and RIGHT functions but required for MID.

answered Sep 16 '16 at 15:32

G42

9,791
2
19
34

Careful - this works if the second string is the RFD number. If it's somewhere else, it doesn't work. – BruceWayne Sep 16 '16 at 15:37
Worked great, but the data returns as RonaldJimmy Green 34 x 55 Large, how to add a space in between the names? – nick-w-nick Sep 16 '16 at 15:39
@TayseNick - `=LEFT(a1,FIND(" ",a1)-1)&" "&MID(a1,FIND(" ",a1,FIND(" ",a1)+1),99)` – BruceWayne Sep 16 '16 at 15:41
@BruceWayne Works great! I changed a small part of it since that formula gave me an extra space between Ronald and Jimmy. `=LEFT(A1,FIND(" ",A1)-1)&" "&B1&MID(A1,FIND(" ",A1,FIND(" ",A1)+1),99)` Thanks! I really appreciate the help! – nick-w-nick Sep 16 '16 at 15:51

score 0 · Answer 2 · edited May 23 '17 at 11:51

If you're okay with VBA, here's a solution using RegEx.

Function simpleCellRegex(Myrange As Range) As String
    ' Shamelessly lifted from https://stackoverflow.com/a/22542835/4650297
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "[R][F][D]...."

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

So, if cell A1 is "Ronald Blue RFD8827 21 x 25 Small", in B1, just do =simpleCellRegex(A1). Make sure the Regex reference is added, see Step 1 here to do that.

One benefit here, is that the RFD#### can be anywhere in the cell, and it'll get removed. You can probably get this with formulas too, but I am thinking they'd get pretty convoluted and tricky.

Thanks, your previous solution worked great. I am not familiar with VBA though. I really appreciate it. — nick-w-nick, Sep 16 '16 at 15:52
@TayseNick - no worries. You'd just put that VBA into a macro. [Here's a page showing how](https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/), then run `=simpleCellRange(a1)` in Excel, as if it were `=Sum()`, etc. — BruceWayne, Sep 16 '16 at 15:53

How to replace the middle of an Excel cell?

2 Answers2

Linked