To Pull out Certain Set of Data from a Cell

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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 !
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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?
 
Upvote 0
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),"-")
 
Upvote 0
Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

I believe this macro will do what you are asking for...
Code:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
 
Upvote 0
Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

I believe this macro will do what you are asking for...
Code:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]

That is really a nice bit of code which elegantly solves the problem. I truly wish I could understand it....

Jim
 
Upvote 0
Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

That is really a nice bit of code which elegantly solves the problem.
Thanks!



I truly wish I could understand it....
See if this helps any...
Rich (BB 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:
Upvote 0
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... :confused: Thank the Lord of the Worlds & a vbx master from vbxpress helped me sort the issue :)

Thank you for your support and care.
 
Upvote 0
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.
 
Upvote 0
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...
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



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.



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.




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:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
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:
Upvote 0
Re: To Pull out Certain Set of Data from a Cell [ Urgent ]

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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top