Conditional extraction of number references from text contained in single cell

[Noodles90]

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:

-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:

-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]

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 :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Noodles90]

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.

 

[Hui]

=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]

@Hui , Thanks a lot!

 

[Noodles90]

Found today a solution with code too

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

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