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.