Excel - How to Generate a Text String From a "Code" in Another Cell

Question

Hello everyone,

I'm looking to generate a text string in one cell from a "code" I typed in another cell instead of having to manually type it out each time.

For example...

P10S1AS01 would be "Plant 10 Store 1 Section A Shelf 01"

where I want "Plant 10 Store 1 Section A Shelf 01" to be automatically generated in another cell from the cell containing "P10S1AS01" instead of manually typing it out, if that makes sense.

Not sure if there's a known way to accomplish this?

Thanks!

Answer 1

Perfect, really appreciate the help.

If I DID subtract a part of the code on a future cell (such as taking S01 off of P10S1AS01, so it reads P10S1A), it will still say "Shelf" at the end. Is there a way to exclude this if no character is present (outside of deleting the part of the formula that says "Shelf")?

Answer 2

Excellent, this works perfectly!

If I subtracted a part of the code on a future cell (such as taking S01 off of P10S1AS01, so it reads P10S1A), it will still say "Shelf" at the end. Is there a way to exclude this if no character is present?

Answer 3

Will it always be "Plant nn Store nn Section nn Shelf nn"? If so, create the following custom function in a new module in the Visual Basic Editor:

Function ParseCode(s As String) As String
    Dim p1 As Long
    Dim p2 As Long
    Dim m As String
    Dim i As Long
    On Error GoTo ExitHere
    p1 = InStr(s, "S")
    p2 = InStrRev(s, "S")
    m = Mid(s, p1 + 1, p2 - p1 - 1)
    For i = 1 To Len(m)
        If Not IsNumeric(Mid(m, i, 1)) Then
            Exit For
        End If
    Next i
    ParseCode = "Plant " & Mid(s, 2, p1 - 2) & " Store " & Left(m, i - 1) & _
        " Section " & Mid(m, i) & " Shelf " & Mid(s, p2 + 1)
ExitHere:
End Function

Let's say you enter the code in A2.

In B2, enter the formula

=ParseCode(A1)

and fill down.

Save the workbook as a macro-enabled workbook (.xlsm), and make sure that you allow macros when you open it.