Extract content of a single cell and separate and rename accordingly?-CodePudding

So I have this large excel file, over 7000 rows, and what I need to do is pick the content from a cell in a column, for example A2, and extract its content onto A3

The thing is that the content are abbreviatures, like PRD for period, or CLS for class, all abbreviature are separated by underscores with no particular order, so for example A2 can just say PRD but B2 would say PRD_CLS_PPRD_ADVAN and then back to CLS_ADV on C2

What I need is to extract the content from each cell an put it on another cell, the abbreviatures must be replaced by it's original word so instead of PRD it should say Period, or Class instead of CLS, when there's an underscore it should be replaced for a slash. So overall when B2 says PRD_CLS_PPRD_ADVAN then B3 should say Period/Class/Pre-Production/Advance

I've given it a lot of tries to solve this, using LEFT, RIGHT, EXTRACT, but to no avail, would appreciate any suggestion on how to solve this

CodePudding user response：

'something like that should get you started

Public Sub convertCell()
Dim rowmax As Integer
Dim i As Integer
Dim j As Integer
Dim StrArray() As String
Dim wrds As Integer


'replace sheet1 by the name of you sheets

rowmax = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To rowmax
    StrArray = Split(Worksheets("Sheet1").Cells(i, 1), "_")
    wrds = UBound(StrArray)
    
    For j = 0 To wrds
        Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet1").Cells(i, 2) & "/" & StrArray(j)
    Next j
    
Next i

End Sub

CodePudding user response：

So basic method using formulae:

Formulae in C2:

VLOOKUP(LEFT(A2,FIND("_",A2,1)-1),$E$2:$F$5,2,0)

Cells E2 to F5 hold the abbreviations, extend the range as needed.

So, update, been playing.

Just done the first two, use mid() for number 3 and right for number 4.

CodePudding user response：

So, slightly different approach using find():

Formula for C2:

IFERROR(IF(FIND($E$2,$A2,1)>0,VLOOKUP($E$2,$E$2:$F$5,2,0),"")&"/","")&IFERROR(IF(FIND($E$3,$A2,1)>0,VLOOKUP($E$3,$E$2:$F$5,2,0),"")&"/","")&IFERROR(IF(FIND($E$4,$A2,1)>0,VLOOKUP($E$4,$E$2:$F$5,2,0),"")&"/","")&IFERROR(IF(FIND($E$5,$A2,1)>0,VLOOKUP($E$5,$E$2:$F$5,2,0),""),"")

Let you think about the trailing "/"...