Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Hello,
I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Hi Frank
maybe not the cleanest formula, but it works: Column A your Names Column B: First Name: =LEFT(A1,FIND(" ",A1)-1) Column C: Middle Name: =IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),"",MID(A1,LEN(B1)+2,LEN(A1)- LEN(B1)-LEN(D1)-2)) Column D: Last Name: =IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))) pay attention, the formulas are long, so they might wrap while posting. each formula for each column has to be on one line! I'm pretty sure, somebody has an easier solution, but until then... hth Carlo On Dec 18, 2:46 pm, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Frank,
You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Thanks Kalpesh for your quick response, however I have tried to look all
excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Kalpesh's solution is in VBA, because you posted it in the programming
section. I figured that you wanted to achieve this with formulas, that's where my approach comes from. hth Carlo On Dec 18, 4:56 pm, Frank Situmorang wrote: Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
just to expand on kalpesh's response, his solution is vba since this is a
programming newsgroup. try this code. it assumes your names are in column A starting in Row1 on sheet1 and the names are split in columns B, C & D in the same row just change the references to whatever you need Sub split_text() Dim data As Variant Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lastrow data = Split(Range("A" & i).Value, " ") ws.Range("B" & i) = data(0) If UBound(data) = 2 Then ws.Range("C" & i) = data(1) ws.Range("D" & i) = data(2) Else ws.Range("D" & i) = data(1) End If Next End Sub -- Gary "Frank Situmorang" wrote in message ... Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Thank you Carlo, you are right, I do not use VBA and not to familiar with it,
I am an accountant in fact, It works now for the first name using your formula, but now I am trying to figure out how Middle and last name formula can work. Thank you very much, if you could explain a bit more. How if we use RIGHT function and always refer to the same Cell A1 -- H. Frank Situmorang "carlo" wrote: Kalpesh's solution is in VBA, because you posted it in the programming section. I figured that you wanted to achieve this with formulas, that's where my approach comes from. hth Carlo On Dec 18, 4:56 pm, Frank Situmorang wrote: Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Gary:
Could you educate me how can I use VBA to do it. Should I assin a kind of macro, where is the control Thanks in advance -- H. Frank Situmorang "Gary Keramidas" wrote: just to expand on kalpesh's response, his solution is vba since this is a programming newsgroup. try this code. it assumes your names are in column A starting in Row1 on sheet1 and the names are split in columns B, C & D in the same row just change the references to whatever you need Sub split_text() Dim data As Variant Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lastrow data = Split(Range("A" & i).Value, " ") ws.Range("B" & i) = data(0) If UBound(data) = 2 Then ws.Range("C" & i) = data(1) ws.Range("D" & i) = data(2) Else ws.Range("D" & i) = data(1) End If Next End Sub -- Gary "Frank Situmorang" wrote in message ... Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
in excel, press alt-F11 to enter the vb editor
then control-R to view the explorer window if it's not visible you can either double click the sheet name and paste the code in the window that opens or right click in a black area below the sheets names, choose insert then module. paste the code there once you've done that, you can run the code from the vb window, or close it, press alt-F8 and run the macro from there. -- Gary "Frank Situmorang" wrote in message ... Gary: Could you educate me how can I use VBA to do it. Should I assin a kind of macro, where is the control Thanks in advance -- H. Frank Situmorang "Gary Keramidas" wrote: just to expand on kalpesh's response, his solution is vba since this is a programming newsgroup. try this code. it assumes your names are in column A starting in Row1 on sheet1 and the names are split in columns B, C & D in the same row just change the references to whatever you need Sub split_text() Dim data As Variant Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lastrow data = Split(Range("A" & i).Value, " ") ws.Range("B" & i) = data(0) If UBound(data) = 2 Then ws.Range("C" & i) = data(1) ws.Range("D" & i) = data(2) Else ws.Range("D" & i) = data(1) End If Next End Sub -- Gary "Frank Situmorang" wrote in message ... Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Carlo,
I need to tell you that the names some times there is only 2 pieces and some times 3 pieces, how can we cope with this situation Thanks in advance -- H. Frank Situmorang "carlo" wrote: Kalpesh's solution is in VBA, because you posted it in the programming section. I figured that you wanted to achieve this with formulas, that's where my approach comes from. hth Carlo On Dec 18, 4:56 pm, Frank Situmorang wrote: Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Thank you Gary, I will give a try
-- H. Frank Situmorang "Gary Keramidas" wrote: in excel, press alt-F11 to enter the vb editor then control-R to view the explorer window if it's not visible you can either double click the sheet name and paste the code in the window that opens or right click in a black area below the sheets names, choose insert then module. paste the code there once you've done that, you can run the code from the vb window, or close it, press alt-F8 and run the macro from there. -- Gary "Frank Situmorang" wrote in message ... Gary: Could you educate me how can I use VBA to do it. Should I assin a kind of macro, where is the control Thanks in advance -- H. Frank Situmorang "Gary Keramidas" wrote: just to expand on kalpesh's response, his solution is vba since this is a programming newsgroup. try this code. it assumes your names are in column A starting in Row1 on sheet1 and the names are split in columns B, C & D in the same row just change the references to whatever you need Sub split_text() Dim data As Variant Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To lastrow data = Split(Range("A" & i).Value, " ") ws.Range("B" & i) = data(0) If UBound(data) = 2 Then ws.Range("C" & i) = data(1) ws.Range("D" & i) = data(2) Else ws.Range("D" & i) = data(1) End If Next End Sub -- Gary "Frank Situmorang" wrote in message ... Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Thanks very much Carlo, it works ok now for me
-- H. Frank Situmorang "carlo" wrote: Kalpesh's solution is in VBA, because you posted it in the programming section. I figured that you wanted to achieve this with formulas, that's where my approach comes from. hth Carlo On Dec 18, 4:56 pm, Frank Situmorang wrote: Thanks Kalpesh for your quick response, however I have tried to look all excel function, I can not found split function, could you explain again on how can I handle it? Thanks very much -- H. Frank Situmorang "Kalpesh" wrote: Frank, You can use Split function to break the string into pieces seperated by space. So, a string with Rone Aprilia Gultom will return an array with length 3 e.g. data = Split("Rona Aprilia Gultom", " ") data(0) - will return Rona data(1) - will return Aprilia data(2) - will return Gultom The length of array will change depending upon the spaces inside the name. I am assuming, things are seperated by space. HTH Kalpesh On Dec 18, 10:46 am, Frank Situmorang wrote: Hello, I want to finetuning my chruch records membership With 500 members some names are 2 or 3 pieces words and now I want to put them into separate names since I want to build a Access database instead of excell this is example of my problem In excel iin one Cell Rona Aprilia Gultom, I want to separate it into 3 Cell Fuirst Name: Rona Middle Name: Aprilia Last Name: Gultom My problem some is 2 pieces and some are 3 and the length of the name is variable so I can not use Mid function in this matter. What is certain is there is alwasy "space" between the words of the name Can anyone help me how to do splitting this names? Thanks in advance, Frank -- H. Frank Situmorang- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Frank
Maybe you could work with DataText to ColumnsDelimited by space. Gord Dibben MS Excel MVP On Tue, 18 Dec 2007 01:21:00 -0800, Frank Situmorang wrote: Thank you Gary, I will give a try |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Thanks very much Gord, you gave me a simple way.
-- H. Frank Situmorang "Gord Dibben" wrote: Frank Maybe you could work with DataText to ColumnsDelimited by space. Gord Dibben MS Excel MVP On Tue, 18 Dec 2007 01:21:00 -0800, Frank Situmorang wrote: Thank you Gary, I will give a try |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to split 2 or 3 pieces name in 1 cell into 2 or 3 cells
Hi Frank
just wanted to tell you, that Gord's solution is pretty neat, although you have to pay attention. The way your data is set up right now, the lastname of your 2piecenames and the middlename of your 3piecenames will be in the same column....maybe not the best if you want to export the data to a database, as you said in your first post. In my first post i gave you formulas for all 3 columns, which will put the last name always in column d and fill out column c (middlename) only if there is a middlename. Cheers Carlo If you have other questions, just ask. On Dec 19, 10:40 am, Frank Situmorang wrote: Thanks very much Gord, you gave me a simple way. -- H. Frank Situmorang "Gord Dibben" wrote: Frank Maybe you could work with DataText to ColumnsDelimited by space. Gord Dibben MS Excel MVP On Tue, 18 Dec 2007 01:21:00 -0800, Frank Situmorang wrote: Thank you Gary, I will give a try- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I split 1 cell into 2 cells in Excel? | Excel Discussion (Misc queries) | |||
Is there any way to split contents of a cell into more cells. | Excel Discussion (Misc queries) | |||
How do I split one cell of 6 numbers into two cells of 3 and 3? | Excel Discussion (Misc queries) | |||
How to split the contents of a cell between two cells. | Excel Worksheet Functions | |||
I need to split first & last name cell into two different cells. . | Excel Programming |