• 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.

extracting user-defined id numbers from within a cell

J

J.Steik

New Member
Hello everyone - I am trying a several step process which step 1 is to extract the equipment ID's from within a cell that is text/comments...I have attatched a workbook showing a before and an after sheet - the numbers I'm trying to extract are always numbers with a 2 or 3 capital letter tag nested.. can I somehow pull these out of each comments box and make a list as the example is showing? I'm a newbie and I'm trying all kinds of variations on the MID function and am having a very difficult time. Any help would be greatly appreciated!
 

Attachments

  • extract data.xlsx
    11.3 KB · Views: 13
Dear J.Steik

As per your attachment, in sheet "Objective" in Column "D", contains four Ids i.e HD120,PQ120,L120G & WL430. In column "A" you have mentioned only one Id and in next cell you wrote second id and in next cell you wrote third Id. Is this correct way of your requirement like if in a cell it has 5 Ids, then it should split into different cells one by one (down words).Please confirm.

Next one is, Is there any identification of IDs, so that we can work and try to pull it from the cell.

Please let me know
 
Narayank - that is pretty much what I am trying to accomplish! Thank you! I am t4rying it out on a few other comment cells and it works except I can't seem top make columns G,H,I and J list the contents of the cell in column F (?) And yes Vijay, the only way I can see to identify the equipment ID is by their having a combination of Capital latters and numbers. I have a "tag list" also that I thought could be useful - it is a list of all the 2-letter values the id numbers would possibly contain. I thought that using that as a reference somehow(?)
 
Hi,

If VBA is acceptable then you can try following UDF (User Defined Function).

If you do not know how to use UDF then this is brief How to of it:
1. While in Excel, press ALT+F11 simultaneously. This will open Visual Basic Editor window.
2. Then from menu select Insert >> Module. This will insert a new module with name as Module1
3. After step 2, paste following code in the coding pane.
Code: 
Public Function ExtractID(strToCheck As String, intSerial As Integer) As String
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
With objRegExp
  .Global = True
  .IgnoreCase = False
  .MultiLine = True
  .Pattern = " [A-Z]+[0-9]+[A-Z,. ]"
End With
ExtractID = Replace(Replace(CStr(objRegExp.Execute(strToCheck).Item(intSerial - 1).Value), _
",", ""), ".", "")
End Function

After this you can use the function like any other functions. e.g.
=ExtractID($D2,COLUMNS($A$1:A1))
And then copy across columns until you get #VALUE! error.
 
shrivallabha, that is awesome - thank you! I am however, only able to get one result returned from the comment cell... also, a great portion of the Equipment ID numbrs start with numbers as well...such as '46GT702' and such..is this something that could be adjusted in the '.pattern=' portion of the vb code?

I am really grateful for the direction and assistance! I am hoping once I get the extraction figured out I can transpose the ID numbers into a column..with breaks between each group...could a macro accomplish 'automating' this task perhaps?
 
absolutely! This workbook is a fine example. If I can get all the equipment IDs from B2 to populate A2, A3, A4, etc (I think 21 ID numbers arein B2)...insert a break then repeat with B3 to extract into column A, populating after the break ..I hope that makes sense (?)
 

Attachments

  • example.xlsx
    12.8 KB · Views: 8
You are on right path.

Just change the pattern to:
Edit: Revised code
Code: 
Public Function ExtractID(strToCheck As String, intSerial As Integer) As String
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
With objRegExp
  .Global = True
  .IgnoreCase = False
  .MultiLine = True
  .Pattern = " [0-9]*[A-Z]+[0-9]+[A-Z(,. ]"
End With
ExtractID = Trim(Replace(Replace(Replace(CStr(objRegExp.Execute(strToCheck).Item(intSerial - 1).Value), _
",", ""), ".", ""), "(", ""))
End Function

And in the latest posted sample put this formula in cell C2:
=ExtractID($B2,COLUMNS($A$1:A1))

And then copy it down and across until column W. You will see results in separate cells.

I think in Row 2 you want 52LCP704 to be listed but it has little different pattern.
 
Last edited:
Hi Shrivallabha ,

With the following , only H2S seems to be a problematic inclusion.
Code: 
Public Function ExtractID(strToCheck As String, intSerial As Integer) As String
                Dim objRegExp As Object
                Set objRegExp = CreateObject("VBScript.RegExp")
               
                With objRegExp
                    .Global = True
                    .IgnoreCase = False
                    .MultiLine = True
                    .Pattern = " [0-9]+[A-Z]+[0-9]*[A-Z]*| [A-Z]+[0-9]+[A-Z]*[0-9]*"
                End With
                ExtractID = objRegExp.Execute(strToCheck).Item(intSerial - 1).Value
End Function
Narayan
 
I have been toying with your solutions and am very grateful for your help - thank you so very much! Is there a way of using a COUNT function possibly to cause the number extracted ID numbers to insert an equal amount of blank rows below each of the corresponding 'source' cells (B2, B3, etc) ? If can achieve this it will allow me to populate the rows with information pertinent to the equipment numbers extracted :)

I tried a COUNTA function and failed miserably - causing everything to disappear on the worksheet
 
Hi ,

If you want to do this , then it cannot be done using a UDF ; what you need is a subroutine which will operate on each of the cells , extract the pattern matches to successive cells below each of the 'source' cells.

It can be done ; can you check back later ?

Narayan
 
Hi ,

See this file.

Before running the macro , you need to define a named range data_range , which I have at present referred to :

=Sheet1!$B$2:$B$7

You might have to delete those rows which do not correspond to your wanted pattern , like for example the one which has H2S.

Narayan
 

Attachments

  • example (3).xlsm
    19.4 KB · Views: 7
that is absolutely awesome! I need to re-arrange the data some ...ideally I would export the equipment ID's into another worksheet and populate column C with a copy of the originating comment cell...this is a great help for me - thank you!!
 
hello again Narayan - my ineptitude will surely show now: I am trying to extend the range of data_range to include all of column B ...I am at a loss to find that protion of code! can you point me in the right direction?
Thank you! :)
 
Hi ,

As I mentioned in my post , extending the data range is outside of the code ; go to the Name Manager , click on the name data_range , which has been defined in my file ; if you don't have this defined in your file , you need to do it. When doing it , just change the reference in the Refers To box from the existing :

=Sheet1!$B$2:$B$7

to what ever is the extent of your data ; for example , if your data is in column H , and extends till row 73 , the above reference will change to :

=Sheet1!$H$2:$H$73

Narayan
 
hello again Narayan - I have been trying to figure out a small change, was hoping you could advise of what I need to address: I would like to run the macro, but have the comments column be in column J and extract the equipment numbers to column F... I am also trying to find a way of changing the font for every other comment cell to a different color (like red for instance) that way, when the list is generated it will be easier to sort and read.
I tried changing the data_range reference in order to extract the numbers into column F but it doesn't work - any idea how I might accomplish this?
Thank you!!
 
Hi ,

See this file ; the change to column J and column F has been done ; regarding the font color , I'd like to take a little more time.

Narayan
 

Attachments

  • example (3).xlsm
    20.5 KB · Views: 6
Hello again everyone! I have a couple of issues I’m attempting to address with my project and hoped you folks could help me out. In the macro ‘SeparateIDs’ it looks for certain criteria in the Equipment ID it extracts..I need to broaden those criteria to include IDs containing hyphens and parenthesis as some IDs end up having numbers in parenthesis at the end of them. I also need to omit any results 2 characters or less. That brings me to my next hurdle…
..Prior to running the macro ‘SeparateIDs’ there is an equipment ID number in column F on the same row as the cell I’m extracting data from (‘Description’, column J)…that already existing ID in col F most often starts with a 2-digit number: what I’d like to do is when extracting the equipment ID’s from column J into column F, to reference the already existing number in F , if it starts with a 2-digit number then apply that 2-digit prefix to the beginning of the extracted IDs which have been pulled column J
So if F2 has an ID which is 03GT456 and the IDs we extract from J2 meet certain criteria we apply the ‘03’ prefix to the left side of them. The extracted IDs which need the prefix attached need to
· not be an ID which contains a hyphen
· be greater in length than 3 characters
So if the ID extracted from J2 was ‘LC456’, it would then be ‘03LC456’and so forth. If it was ‘V-456’ it would be left unchanged, but still extracted. If it was ‘GT’ it would not be extracted, and so forth.
I’ve tried experimenting with different expressions in the macro but am not certain I am using the ‘[‘ correctly and have failed miserably.
Is this something I can do with some modifications of the current macro?
Once again, thank you!
 

Attachments

  • most recent effort.xlsm
    176.9 KB · Views: 3
You must log in or register to reply here.
Back
Top