Click here to Skip to main content
15,561,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I am new to VBA and trying to Extract the String from the Data available in the Cell.

Cell"A2"
Young, Yamen (09/21/1960 M)


Cell"A5"
Insurance: CHIP(U4024563201)   Location:  (Far Rockaway)   Type: Radiology - Results Follow-up


I want to extract these details from above data
Young, Yamen	9/21/1960	CHIP	U4024563201


What I have tried:

Here is my code that i have tried so far. Any help will be apprecaited.

Dim ws As Worksheet
Dim val As String
Dim val2 As String
Set ws = Sheet1

val = ws.Range("A2")
val2 = ws.Range("A5")
On Error Resume Next

ws.Range("B28") = Split(val, "(")(0)
ws.Range("C28") = Right(val, Len(val) - (InStrRev(val, "(")))
ws.Range("D28") = Left(val2, Len(val2) - (InStrRev(val2, ":")))
ws.Range("E28") = Left(val2, Len(val2) - (InStrRev(val2, ")")))
Posted
Updated 21-May-21 13:04pm
v2
Comments
CHill60 21-May-21 12:48pm    
What is the problem you are facing with the code that you have?
ShoRaj 21-May-21 13:00pm    
Problem is that my code is not extracting the Exact information
The Other John Ingram 21-May-21 15:51pm    
what is the value of B28?
RedDk 21-May-21 16:25pm    
Well, I'd start by normalizing your data. That is ask yourself what does it represent. Then formally make it pretty. For example you've started off with two cells of content. I'd have started off by making all the content "normal" (same spacing for each "item") because I know how useful a "token" is when dealing with "parsing" of that data. I see here Name: ... and that goes along with Insurance: and Location: and Type: ... follow?

So, start out with a one CELL:

"Name: Young_Yamen, BD: 09/21/1960, Sex: M, Insurance: CHIP(U4024563201), Location: Far Rockaway, Type: Radiology - Results Follow-up"

1 solution

Here's some logic that follows my suggestion above:
Sub splitstringCPQA()
    
    Dim ws As Worksheet
    Set ws = Sheet1
   
    Dim Name, col As String
    Dim pos, count As Integer
    Dim stringtosplit As String

    stringtosplit = "Name: Young_Yamen, BD: 09/21/1960, Sex: M, Insurance: CHIP(U4024563201), Location: Far Rockaway, Type: Radiology - Results Follow-up"
    ws.Range("A44") = stringtosplit

    count = 0
        While Len(stringtosplit) > 0
            pos = InStr(stringtosplit, ": ")
            stringtosplit = Mid(stringtosplit, pos + 1, Len(stringtosplit) - pos)
            count = count + 1
            col = "BCDEFG"
            If pos < 1 Then GoTo Error
            If InStr(stringtosplit, ",") > 0 Then
                ws.Range(Mid(col, count, 1) + "45") = Left(stringtosplit, Len(stringtosplit) - (Len(stringtosplit) - InStr(stringtosplit, ",") + 1))
            End If
        Wend
Error:
            ws.Range("G45") = stringtosplit
            End
End Sub

Use the debugger and copious Debug.Print statements to see what's going on.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900