• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 25
  • Last Modified:

Extract A String based on special Characters

Hello , I wish your help on the attached example
I have cell in excel Column A contain string of text along with number
I want to extract each value between the special characters to separate column
I made manual example for how the result must be and hope to use formula to save the time
Thanks in advance for your help
Iso.xlsx
0
M. Saad
Asked:
M. Saad
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...

Function getString(ByVal str As String, bin As Integer) As String
Dim arr() As String
str = Replace(Replace(Replace(str, "-", "^"), "/", "^"), "_", "^")
arr = Split(str, "^")
getString = arr(bin - 1)
End Function

Open in new window

And then use if on the sheet like this...
In B2
=getString($A2,COLUMNS($B2:B2))

Open in new window

and then copy it across and down.
Iso.xlsm
1
 
Martin LissOlder than dirtCommented:
Or you can use this macro.
Sub CreateColumns()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String
Dim intPart As Integer
Dim varSpecial As Variant
Dim intS As Integer

' Add any other special characters here
varSpecial = Array("/", "_")

lngLastRow = Range("A1048576").End(xlUp).Row

With ThisWorkbook.Worksheets("Sheet1")
    For intS = 0 To UBound(varSpecial)
        .UsedRange.Cells.Replace What:=varSpecial(intS), Replacement:="-"
    Next
    For lngRow = 2 To lngLastRow
        strParts = Split(.Cells(lngRow, "A"), "-")
        For intPart = 0 To UBound(strParts)
            .Cells(lngRow, intPart + 2) = strParts(intPart)
        Next
    Next
End With
End Sub

Open in new window

1
 
Rob HensonFinance AnalystCommented:
Or a series of MID and FIND functions:

UNIT
=LEFT(A2,FIND("-",A2,1)-1)
or
=MID(A2,1,FIND("-",A2,1)-1)

FLOW
=MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND("-",A2,1)-1)

NO
=MID(A2,FIND("-",A2,FIND("-",A2,1)+1)+1,FIND("/",A2,1)-FIND("-",A2,FIND("-",A2,1)+1)-1)

WBS
=MID(A2,FIND("/",A2,1)+1,FIND("_",A2,1)-FIND("/",A2,1)-1)

TYPE
=MID(A2,FIND("_",A2,1)+1,LEN(A2)-FIND("_",A2,1))

See attached.
Iso.xlsx
0
 
M. SaadAuthor Commented:
thank you all for your big help , very helpful and solved my problems
i will go with the formulas by Mr. Rob
many thanks again for your support
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now