Results 1 to 10 of 10

To Pull out Certain Set of Data from a Cell [ Urgent ]

This is a discussion on To Pull out Certain Set of Data from a Cell [ Urgent ] within the Excel Questions forums, part of the Question Forums category; Hi Good Evening from Sri Lanka, Guys I have a massive challenge here, I normally wouldn't post URGENT, If it ...

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    22

    Default To Pull out Certain Set of Data from a Cell [ Urgent ]

    Hi Good Evening from Sri Lanka,

    Guys I have a massive challenge here, I normally wouldn't post URGENT, If it isn't.

    Please help if possible.

    I am only looking for a UDFs or Formulas. Not interested in Macro as lots of sensitive data and numbers are present and I don't want to be in a mess. UDFs or a formulaS would easily help me Target what I need here.

    Data I have :-

    Cell A1 = Phone: 81-74568943 Fax: -
    Cell A2 = Phone: 51-83-9498756, 5583772359 Fax: 61-88-4932516
    Cell A3 = Phone: 51-6785239556, Fax: 31-13-45225335
    Cell A4 = Phone: 51-6785239556, Fax: 31-13-45225335, Mobile: -

    What I need :-

    B1 = Phone (As a data which we have pulled out from A1)
    C1 = 81-74568943
    D1 = Fax
    E1 = - (Or N/A - Not the N/A Error Message or a value! error message, But just the letters : N/A)
    F1 = Mobile
    G1 = - (Or N/A - Not the N/A Error Message or a value! error message, But just the letters : N/A)

    Similarly, I need to get the same outcome for the A2:A4,

    I wouldn't mind 2 contact Numbers in the same cell with a coma separating it. ex : Situation in A2

    Please Help Me. Thank You so Much Every Lady and Gentleman out there !

  2. #2
    New Member
    Join Date
    Apr 2017
    Posts
    22

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    A brother helped me with the below elsewhere to get the above Job done, where as I have to select 6 cells and hit ctrl + Shift + Enter (which makes it an Array formula). I am unable to get in touch with his as he is offline.

    The formula is as follows, could someone teach me how to remodel the same to get the following result?

    A1=Contact Person: James Anderson Designation: Proprietor >>> to B1=Contact Person, C1=James Anderson, D1=Designation, E1=Proprietor

    ?

    Option Explicit
    Function BreakOut(s As String) As Variant
    Dim v As Variant
    Dim aOut As Variant
    Dim i As Long
    Dim s1 As String

    Application.Volatile

    s1 = s


    s1 = Replace(s1, ":", vbNullString)

    If s1 Like "*[0-9], [0-9]*" Then
    s1 = Replace(s1, ", ", Chr(1))
    End If

    v = Split(s1, " ")

    ReDim aOut(0 To 5)
    For i = LBound(aOut) To UBound(aOut)
    aOut(i) = vbNullString
    Next I


    For i = LBound(v) To UBound(v)
    v(i) = Replace(v(i), Chr(i), ", ")

    If v(i) = "-" Then
    aOut(i) = "N/A"
    ElseIf Right(v(i), 1) = "," Then
    aOut(i) = Left(v(i), Len(v(i)) - 1)
    Else
    aOut(i) = v(i)
    End If
    Next I

    BreakOut = aOut
    End Function

    Formatting tags added by mark007





    How should I remodel the above?

  3. #3
    Board Regular auto.pilot's Avatar
    Join Date
    Sep 2007
    Location
    Western hemisphere
    Posts
    602

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    This works with the small sample you provided, but it appears that your data is not consistent from one row to the next. Example: The word Fax sometimes has a comma in front of it and other times not. The formulas consider only the data shown. It will of course fail if your data has other formats.


    Cell B1: Phone
    Cell C1: =IFERROR(MID($A1,FIND("Phone:",$A1)+7,FIND(", Fax:",$A1)-FIND("Phone:",$A1)-7),MID($A1,FIND("Phone:",$A1)+7,FIND("Fax:",$A1)-FIND("Phone:",$A1)-7))
    Cell D1: Fax
    Cell E1: =IFERROR(MID($A1,FIND("Fax:",$A1)+5,FIND(", Mobile:",$A1)-FIND("Fax:",$A1)-5),RIGHT($A1,LEN($A1)-FIND("Fax:",$A1)-4))
    Cell F1: Mobile
    Cell G1: =IFERROR(RIGHT($A1,LEN($A1)-FIND("Mobile:",$A1)-7),"-")
    Student Driver, still much to learn.

    [CODE] Place Code Here [/CODE ]

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    27,755

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    I believe this macro will do what you are asking for...
    Code:
    Sub PhoneFaxMobile() Dim R As Long, X As Long, Z As Long, Data As Variant, Results As Variant Dim Parts() As String, SubParts() As String Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ReDim Results(1 To UBound(Data), 1 To 6) For R = 1 To UBound(Data) Data(R, 1) = Replace(Replace(Replace(Data(R, 1), "Phone", "|Phone"), "Fax", "|Fax"), "Mobile", "|Mobile") Parts = Split(Data(R, 1), "|") For Z = 1 To 6 Results(R, Z) = Split("Phone N/A Fax N/A Mobile N/A")(Z - 1) Next For X = 1 To UBound(Parts) SubParts = Split(Parts(X), ":") SubParts(1) = Trim(SubParts(1)) If SubParts(1) <> "-" Then If Right(SubParts(1), 1) = "," Then SubParts(1) = Left(SubParts(1), Len(SubParts(1)) - 1) Select Case SubParts(0) Case "Phone": Results(R, 2) = SubParts(1) Case "Fax": Results(R, 4) = SubParts(1) Case "Mobile": Results(R, 6) = SubParts(1) End Select End If Next Next Range("B1").Resize(UBound(Results), 6) = Results End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  5. #5
    Board Regular auto.pilot's Avatar
    Join Date
    Sep 2007
    Location
    Western hemisphere
    Posts
    602

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Quote Originally Posted by Rick Rothstein View Post
    I believe this macro will do what you are asking for...
    Code:
    Sub PhoneFaxMobile() Dim R As Long, X As Long, Z As Long, Data As Variant, Results As Variant Dim Parts() As String, SubParts() As String Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ReDim Results(1 To UBound(Data), 1 To 6) For R = 1 To UBound(Data) Data(R, 1) = Replace(Replace(Replace(Data(R, 1), "Phone", "|Phone"), "Fax", "|Fax"), "Mobile", "|Mobile") Parts = Split(Data(R, 1), "|") For Z = 1 To 6 Results(R, Z) = Split("Phone N/A Fax N/A Mobile N/A")(Z - 1) Next For X = 1 To UBound(Parts) SubParts = Split(Parts(X), ":") SubParts(1) = Trim(SubParts(1)) If SubParts(1) <> "-" Then If Right(SubParts(1), 1) = "," Then SubParts(1) = Left(SubParts(1), Len(SubParts(1)) - 1) Select Case SubParts(0) Case "Phone": Results(R, 2) = SubParts(1) Case "Fax": Results(R, 4) = SubParts(1) Case "Mobile": Results(R, 6) = SubParts(1) End Select End If Next Next Range("B1").Resize(UBound(Results), 6) = Results End Sub
    That is really a nice bit of code which elegantly solves the problem. I truly wish I could understand it....

    Jim
    Student Driver, still much to learn.

    [CODE] Place Code Here [/CODE ]

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    27,755

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Quote Originally Posted by auto.pilot View Post
    That is really a nice bit of code which elegantly solves the problem.
    Thanks!



    Quote Originally Posted by auto.pilot View Post
    I truly wish I could understand it....
    See if this helps any...
    Code:
    Sub PhoneFaxMobile() Dim R As Long, X As Long, Z As Long, Data As Variant, Results As Variant Dim Parts() As String, SubParts() As String ' This line of code moves all the data from cell A1 down to the last cell ' in Column A with data into array in memory where it can be processed ' faster. An array created this way is always two-dimensional even the ' range consist of one column as it does here Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ' Here I dimension an array in memory to house the output data... ' it has as many rows as the original data and is 6 columns wide ReDim Results(1 To UBound(Data), 1 To 6) ' Here we start a loop to examine and process each rows (cells) data For R = 1 To UBound(Data) ' Here I replace the words Phone, Fax and Mobile with those same words ' prefixed by a character that will not appear in the normal data from ' Column A which will be used later on to split the text apart... the ' R represents the row inside the array and the 1 is column number... ' remember I said above, even though the original data is only one ' columm wide, a two-dimensional array will be created in memory, so ' the 1 accounts for that forced second dimension Data(R, 1) = Replace(Replace(Replace(Data(R, 1), "Phone", "|Phone"), "Fax", "|Fax"), "Mobile", "|Mobile") ' Here I am splitting the data apart using that character I prefixed ' onto the words Phone, Fax and Mobile as mentioned above Parts = Split(Data(R, 1), "|") ' This loop load the Results array for the data row we are currently ' working on... Phone, Fax and Mobile go into the first, third and ' fifth array column values and N/A fills the second, fourth and sixth ' element positions For Z = 1 To 6 Results(R, Z) = Split("Phone N/A Fax N/A Mobile N/A")(Z - 1) Next ' We not look at each element of the split apart row data with the idea ' that we will fill the appropriate Results array elements with them ' for the current row For X = 1 To UBound(Parts) ' Each keyword (Phone, Fax, Mobile) is followed by a colon, so we ' split the record apart using that delimiter. This will give us a ' two-element array with either the word Phone, Fax or Mobile in ' the first element and the number or comma delimited numbers in ' the second element SubParts = Split(Parts(X), ":") ' The Split function always creates as zero-based array. The first ' element (index number 0) was created to contain either exactly the key ' word Phone, Fax or Mobile; however, the second element (index ' number 1) can have leading and/or trailing spaces, so we trim them off SubParts(1) = Trim(SubParts(1)) ' If the second element consists of a single dash, we ignore it (the ' name and N/A was put into the Results element earlier, so skipping ' just means that earlier data will be displayed If SubParts(1) <> "-" Then ' Since the first element is not a single dash, we need to see if ' it ends with a comma and, if it does, remove the comma If Right(SubParts(1), 1) = "," Then SubParts(1) = Left(SubParts(1), Len(SubParts(1)) - 1) ' Now we see what key word we are working with by checking what is ' in the first element and placing what is in the second element ' into the correct Results array element corresponding to the ' column for that key word Select Case SubParts(0) Case "Phone": Results(R, 2) = SubParts(1) Case "Fax": Results(R, 4) = SubParts(1) Case "Mobile": Results(R, 6) = SubParts(1) End Select End If Next Next ' Here we place the entire Results array into the cells that' ' are supposed to house it Range("B1").Resize(UBound(Results), 6) = Results End Sub
    Last edited by Rick Rothstein; Apr 21st, 2017 at 05:05 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  7. #7
    New Member
    Join Date
    Apr 2017
    Posts
    22

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Yes Auto pilot, That is where my biggest worry is... the one to complain is the ones who had input the data when they had created the profiles, who I do not have any control of... Thank the Lord of the Worlds & a vbx master from vbxpress helped me sort the issue

    Thank you for your support and care.

  8. #8
    New Member
    Join Date
    Apr 2017
    Posts
    22

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Thanks a lot for your kind Support and from vb express another user managed to provide me a solution with a formula which needed me to select 6 cells, enter the code in the first cell and then hit ctrl+shift+enter to make the UDF apply to all 6 cells. My excel sheet is so much filled with unprocessed data and a Macro is a scary thing for me as I do not understand how it works. As its a risk to have lost even a single digit from the same colomn from another data which I don't expect to. Therefore I am planning to go along with that.

    Thank you again for making time for me and auto pilot in not only helping a person solve an issue but also explain how it works respectively.

    May You be blessed more and more Sir.

    Have a Great Day.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    27,755

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Quote Originally Posted by Please_H View Post
    Thanks a lot for your kind Support and from vb express another user managed to provide me a solution...
    First, before I start, an admonishment. Please read (and digest) the article at the following link which will detail what you did wrong and why...

    Excelguru Help Site - A message to forum cross posters



    Quote Originally Posted by Please_H View Post
    My excel sheet is so much filled with unprocessed data and a Macro is a scary thing for me as I do not understand how it works.
    You have nothing to be afraid of from a macro. Underneath it all, macros and UDFs are really the same thing... VBA code manipulating data. The difference between them is how that code is used. The benefit of a UDF is it updates the result it displays nearly immediately if you make an edit to a cell it is dependent on. The cost for this "live update" ability is the cell contains a formula that Excel must maintain and, in your case, that formula is a array-entered formula (one which is confirmed with CTRL+SHIFT+ENTER) which is more resource intensive than a non-array-entered formula. A macro, on the other hand, runs only when you ask it to (for stationary data, that would be once only)... the assumption being that once placed, the data will probably not need to be edited in order to modify it (and if it does, you usually simply run the macro again). The result from a macro is data, not a formula (although it could be if you wanted it to be), so Excel is not burdened by having to maintain multiple formulas to produce the identical results in a range. Perhaps the following will help you become more comfortable in case you want to test out the macro I posted in Message #4 (and commented in Message #6)...

    HOW TO INSTALL MACROs
    ------------------------------------
    If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (PhoneFaxMobile) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

    To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.



    Quote Originally Posted by Please_H View Post
    As its a risk to have lost even a single digit from the same colomn from another data which I don't expect to.
    If designed correctly (and I think my macro is), a macro is just as safe as a UDF. Since the macro I proposed in Message #4 writes to blank cells in Columns B:G, none of your existing data is in any danger of being changed.




    Quote Originally Posted by Please_H View Post
    vb express another user managed to provide me a solution with a formula which needed me to select 6 cells, enter the code in the first cell and then hit ctrl+shift+enter to make the UDF apply to all 6 cells.
    If, after all of the above, you still want to go with a UDF, then I have a simpler, normally entered UDF for you to try. The UDF takes two arguments... the first argument is a text string or a reference to a cell containing such a text string... the second argument is the column offset number to the output column containing the UDF formula. So, passing a 1 to the second argument will have the UDF return the word "Phone", passing a 2 will have it return the actual phone number(s), passing a 3 will have it return the word "Fax", passing it a 4 will have it return the actual Fax number(s), passing a 5 will have it return the word "Mobile" and, finally, passing it a 6 will have it return the actual Mobile number(s). So, you can hard-code these numbers into the UDF formula for each of the six columns you will put the UDF formula in or you can use COLUMNS($B:B) as the second argument in the first formula and copy it across to fill the six columns. Hard-coding the number would be slightly more efficient as Excel would not have to perform a calculation to determine the column offset number. Here is the UDF...
    Code:
    Function BreakOut(ByVal S As String, ColOffset As Long) As Variant Dim Item As String, ColVal As Variant BreakOut = Split("Phone N/A Fax N/A Mobile N/A")(ColOffset - 1) On Error GoTo NotThere If ColOffset Mod 2 = 0 Then Item = Split(Trim(Mid(Split(S & " X:", Split(" Phone Fax Mobile")(ColOffset / 2))(1), 2)), ":")(0) Item = Left(Item, InStrRev(Item, " ") - 1) If Len(Item) > 1 Then BreakOut = Left(Item, Len(Item) + (Right(Item, 1) = ",")) End If NotThere: End Function
    So, to recap, my suggestion is to put these formulas in the indicated cells and copy them down...

    B1: =BreakOut($A1,1)
    C1: =BreakOut($A1,2)
    D1: =BreakOut($A1,3)
    E1: =BreakOut($A1,4)
    F1: =BreakOut($A1,5)
    G1: =BreakOut($A1,6)

    Or, alternately, you can just put this formula in cell B1...

    =BreakOut($A1,COLUMNS($B:B))

    and then copy it across to cell G1, then copy all of those down to the bottom of your data.


    EDIT NOTE: It just occurred to me that you don't need to use a formula for Columns B, D and F as those are the same single words for all the way down for each column. So, in the scenario where I suggest you hard-code the second argument, you can simply put these in the indicated cells before copying down...

    B1: Phone
    D1: Fax
    F1: Mobile

    and still use the indicated formulas for cells C1, E1 and G1.
    Last edited by Rick Rothstein; Yesterday at 02:44 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  10. #10
    New Member
    Join Date
    Apr 2017
    Posts
    22

    Default Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

    Quote Originally Posted by Rick Rothstein View Post
    First, before I start, an admonishment. Please read (and digest) the article at the following link which will detail what you did wrong and why...
    Hi Rick, Good Evening from Sri Lanka...

    With all my Heart I apologize for the negligence from My Part. It was a very urgent situation and I thought less and acted fast. The part which really made me feel sad & Hit me the most was, it said How would I feel if I had worked for Hours to decode a matter and to realize that the one who was in need picked the answer of someone else while both my Time & effort has gone in vein... that part was really painful to Read knowing the amount of effort you had taken Not only to help me, but also to make me understand how the Solution works. More over even after that you had posted more solutions taking a further step in trying to give me a hand in making my work much more easier.

    Mr. Rick, I take pride and Joy in having stumble upon an individual like you.

    May Lord of the Worlds Guide, preserve you and help you out in all your matters. Ameen.

    With Regards to the solution to my Problem, I have saved your work for later reference as I stumbled upon this issue (which is above) at the last stage when I was completing my working Template,

    For now I am working again in my initial stages trying to make my working Template more simpler. Because it had got slow since I fed an existing Supplier list of 8000+....

    I apologize again for my ignorance. And I hope you will apologize this Individual.

    Take Care.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com