Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Cell Range into Multiple Cells
I have a cell range (A1:A4) that contains the following information
When Assigned Organization is XYZ, change Assigned POC to Black, Jack, change Assigned Alt. POC to Doe, Jane, and change Substitute POC to White, Jack When Assigned Department is X-A/7, change Assigned POC to Young, Jim, change Assigned Alt. POC to Brown, Frank, and change Substitute POC to Stewart, Rod When Assigned Group is X-B (XMO/ABC), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve When Assigned Staff is Staffing Personnel (SP), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve I need to split this cell range into several different columns. I need cells B1:B4 to contain the string of characters between “When” and “is”. For example, B1:B4 will contain the following: Assigned Organization Assigned Department Assigned Group Assigned Staff I need cells C1:C4 to contain the string of characters between “is” and the first “,”. C1:C4 will contain the following: XYZ X-A/7 X-B (XMO/ABC) Staffing Personnel (SP) D1:D4 will contain the “Assigned POC”. E1:E4 will contain the “Assigned Alt. POC” and F1:F4 will contain the “Substitute POC” I’ve tried the RIGHT, LEFT, LEN and FIND functions but I can’t get the correct combination to solve this. Can anyone help? Thanks for your help in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Cell Range into Multiple Cells
I would suggest a macro using selectcase. Send file to dguillett1
@gmail.com with this msg and examples On Dec 1, 10:25*am, Willie wrote: I have a cell range (A1:A4) that contains the following information When Assigned Organization is XYZ, change Assigned POC to Black, Jack, change Assigned Alt. POC to Doe, Jane, and change Substitute POC to White, Jack When Assigned Department is X-A/7, change Assigned POC to Young, Jim, change Assigned Alt. POC to Brown, Frank, and change Substitute POC to Stewart, Rod When Assigned Group is X-B (XMO/ABC), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve When Assigned Staff is Staffing Personnel (SP), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve I need to split this cell range into several different columns. *I need cells B1:B4 to contain the string of characters between “When” and “is”. *For example, B1:B4 will contain the following: Assigned Organization Assigned Department Assigned Group Assigned Staff I need cells C1:C4 to contain the string of characters between “is” and the first “,”. *C1:C4 will contain the following: XYZ X-A/7 X-B (XMO/ABC) Staffing Personnel (SP) D1:D4 will contain the “Assigned POC”. *E1:E4 will contain the “Assigned Alt. POC” and F1:F4 will contain the “Substitute POC” I’ve tried the RIGHT, LEFT, LEN and FIND functions but I can’t get the correct combination to solve this. *Can anyone help? Thanks for your help in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Cell Range into Multiple Cells
On Thu, 1 Dec 2011 08:25:51 -0800 (PST), Willie wrote:
I have a cell range (A1:A4) that contains the following information When Assigned Organization is XYZ, change Assigned POC to Black, Jack, change Assigned Alt. POC to Doe, Jane, and change Substitute POC to White, Jack When Assigned Department is X-A/7, change Assigned POC to Young, Jim, change Assigned Alt. POC to Brown, Frank, and change Substitute POC to Stewart, Rod When Assigned Group is X-B (XMO/ABC), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve When Assigned Staff is Staffing Personnel (SP), change Assigned POC to Simon, Paul, change Assigned Alt. POC to Bowie, David, and change Substitute POC to Miller, Steve I need to split this cell range into several different columns. I need cells B1:B4 to contain the string of characters between “When” and “is”. For example, B1:B4 will contain the following: Assigned Organization Assigned Department Assigned Group Assigned Staff I need cells C1:C4 to contain the string of characters between “is” and the first “,”. C1:C4 will contain the following: XYZ X-A/7 X-B (XMO/ABC) Staffing Personnel (SP) D1:D4 will contain the “Assigned POC”. E1:E4 will contain the “Assigned Alt. POC” and F1:F4 will contain the “Substitute POC” I’ve tried the RIGHT, LEFT, LEN and FIND functions but I can’t get the correct combination to solve this. Can anyone help? Thanks for your help in advance The following VBA Macro will parse the contents of each cell in column A in accordance with your specifications, given data as you have presented. Important: As written, it will also clear the contents in columns B:F for any cell in column A that is not blank. This could be changed. sPat is an array that contains the various regular expression patterns to match your text strings and extract the desired substring. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ================================================== Option Explicit Sub ParsePOC() Dim rg As Range, c As Range Dim re As Object, mc As Object Dim s As String Dim i As Long, j As Long Dim sPat As Variant Set rg = Range("A1", Cells(Rows.Count, "A").End(xlUp)) Set re = CreateObject("vbscript.regexp") With re .Global = True .MultiLine = True .ignorecase = True End With sPat = Array("When\s+([\s\S]+?)(?=\s+is)", _ "is\s+([^,]+)", _ "Assigned\s+POC\s+to\s+([\s\S]+?)(?=,\s+change)", _ "Alt\.\s+POC\s+to+\s+([\s\S]+?)(?=,\s+and)", _ "Substitute\s+POC\s+to\s+([\s\S]+)$") Range(rg(1, 2), rg(rg.Rows.Count - 1, 6)).ClearContents For Each c In rg re.Pattern = "[\r\n]+" s = Trim(re.Replace(c.Text, " ")) For i = LBound(sPat) To UBound(sPat) re.Pattern = sPat(i) If re.test(s) = True Then Set mc = re.Execute(s) c(1, i - LBound(sPat) + 2).Value = mc(0).submatches(0) End If Next i Next c End Sub ============================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to split date and time in one cell into multiple cells | Excel Worksheet Functions | |||
Excel 2000 - Split Contents of Cell Across Multiple Cells | Excel Discussion (Misc queries) | |||
split text in one cell into multiple cells without breaking the wo | Excel Worksheet Functions | |||
Combine multiple cells into one cell range. | Excel Worksheet Functions | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |