• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional extraction of number references from text contained in single cell

Noodles90

New Member
Hello! :)

I have following text contained in a single cell at the top of some reports:
Main information
--Menu--
Dimentions and specifications - Dacia Duster
Intref: 05897423654422; v 541 n

C number.: 200000054878 Car ref nr.: 900000058746
Report status: Active Spec report version nr.:3.4
Opt2 nr.: 3.3 Spec report: Active
Last changed: 29-SEP-2021 Modified by: Employee X
And, I needed to extract specific references based on their designations.
There are two scenarios.

Scenario 1:
76479
-The macro searches for the ref "Secref:", in the cell A1 and pastes the value, in this case 05897423654422, in an outside cell in text format (the zeros should remain);
-If there is no "Secref:" reference in the cell, it should search for the "C number" instead and paste the reference in the same outside cell;
-If there is no "C number" either, the macro should write "Not found" in this outside cell.

Scenario 2:
76480
-For a similar kind of report, the macro should use the reference from an outside cell as input, and search for the reference to it associated;
-If found, the value, should be pasted in an outside cell in text format (the zeros should remain);
-If the reference input is not found, then "Not found" should be written in this cell.

Can someone please help?
 

Marc L

Excel Ninja
Hello !​
According to the forum rules :​
  • as this is a wild cross posting you must edit your initial post and share a link for each other forum where you have created the same thread.

  • Attach a workbook sample with a good enough elaboration with details without anything to guess …
A must read :​
 

Noodles90

New Member
Hi Marc L! Thank you for pointing that out, I was not aware of this concept.
I had created the same post in a different forum, but got no replies.
So I've searched for help here. Should have read the rules more carefully though.

As for the source data, that is it, just a single cell containing text with paragraphs like in the initial example.
The names of the cars/parts and references change or might be missing, but always follows the same structure.
At the moment I have to take these references manually and fill a template with a macro that does the next steps.
I don't have the knowledge to program this part unfortunately, extracting the reference numbers only and paste them in an outside cell.
 
Last edited by a moderator:

Hui

Excel Ninja
Staff member
=IFERROR(MID(A1,FIND("C number.:",A1)+10,FIND(" Car",A1)-FIND("C number.:",A1)-10),MID(A1,FIND("ref:",A1)+5,FIND(";",A1)-FIND("ref:",A1)-5))
 

Noodles90

New Member
Found today a solution with code too :)

Code:
Sub Scenario_1()
 With ActiveCell
        If InStr(1, (Range("A1").Value), "Intref:") > 0 Then
            .NumberFormat = "@"
        .Value = Left(Split(Cells(1, 1), "Intref:")(1), 15)
        Else
                    If InStr(1, (Range("A1").Value), "Secref:") > 0 Then
                    .NumberFormat = "@"
                    .Value = Left(Split(Cells(1, 1), "Secref:")(1), 15)
        Else
                        If InStr(1, (Range("A1").Value), "C number.:") > 0 Then
                        .NumberFormat = "@"
                        .Value = Left(Split(Cells(1, 1), "C number.:")(1), 13)
        Else
                            ActiveCell.FormulaR1C1 = "Keyword not found"
        End If
        End If
        End If
        End With
End Sub


Code:
Sub Scenario_2()

Dim RefWord As Variant
RefWord = Cells(4, 3).Value

 With ActiveCell
        If InStr(1, (Range("A1").Value), RefWord) > 0 Then
            .NumberFormat = "@"
        .Value = Left(Split(Cells(1, 1), RefWord)(1), 15)
        Else
        
        ActiveCell.FormulaR1C1 = "Keyword not found"
        End If
   
        End With
End Sub
 
Top