Extracting information from the data

Question

0.00/5 (No votes)

See more:

Excel-2016

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 21-May-21 5:03am

ShoRaj

Updated 21-May-21 12:04pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RedDk · Accepted Answer · 2021-05-21T12:04:00

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.