| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locating the First Comma in a Text
What I am trying to do is find a formula, that will locate the first comma in
a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Merry Xmas
Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Yes I know about that and it works, but it is time consuming, when I have
more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Guess I will have to see if I can find another source for the information
that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Text to Columns is the best solution.........
Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
The answer to your question is yes it does.
Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Ok, that's relatively easy:
J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin Assume that data is in A1:A3 Enter this formula in B1: =LEFT(A1,FIND(",",A1&",")-1) Enter this formula in C1: =IF(B1=A1,"",SUBSTITUTE(A1,B1&", ","")) Select both B1 and C1 then copy down as needed. Biff "caldog" wrote in message ... The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Ok, that's relatively easy:
J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin Assume that data is in A1:A3 Enter this formula in B1: =LEFT(A1,FIND(",",A1&",")-1) Enter this formula in C1: =IF(B1=A1,"",SUBSTITUTE(A1,B1&", ","")) Select both B1 and C1 then copy down as needed. Biff "caldog" wrote in message ... The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Assuming your data in A1:A3
B1 =LEFT(A1,FIND(",",A1)-1) C1 =RIGHT(A1,LEN(A1)-(FIND(",",A1)+1)) Select B1 & C1 and drag down to C3 "caldog" wrote: The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Thanks to both of you for helping me on this problem. It works just perfect.
And my you both have a Merry Christmas. Steve "T. Valko" wrote: Ok, that's relatively easy: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin Assume that data is in A1:A3 Enter this formula in B1: =LEFT(A1,FIND(",",A1&",")-1) Enter this formula in C1: =IF(B1=A1,"",SUBSTITUTE(A1,B1&", ","")) Select both B1 and C1 then copy down as needed. Biff "caldog" wrote in message ... The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
Folks you don't how valuable that formula was to me. It as answer and solved
several forumla problems that I was having, in other aspect in my worksheet. I have tried on several different worksheets, and it has worked everytime. Thanks again so much. Steve "caldog" wrote: Thanks to both of you for helping me on this problem. It works just perfect. And my you both have a Merry Christmas. Steve "T. Valko" wrote: Ok, that's relatively easy: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin Assume that data is in A1:A3 Enter this formula in B1: =LEFT(A1,FIND(",",A1&",")-1) Enter this formula in C1: =IF(B1=A1,"",SUBSTITUTE(A1,B1&", ","")) Select both B1 and C1 then copy down as needed. Biff "caldog" wrote in message ... The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Locating the First Comma in a Text
You're welcome!
Biff "caldog" wrote in message ... Folks you don't how valuable that formula was to me. It as answer and solved several forumla problems that I was having, in other aspect in my worksheet. I have tried on several different worksheets, and it has worked everytime. Thanks again so much. Steve "caldog" wrote: Thanks to both of you for helping me on this problem. It works just perfect. And my you both have a Merry Christmas. Steve "T. Valko" wrote: Ok, that's relatively easy: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin Assume that data is in A1:A3 Enter this formula in B1: =LEFT(A1,FIND(",",A1&",")-1) Enter this formula in C1: =IF(B1=A1,"",SUBSTITUTE(A1,B1&", ","")) Select both B1 and C1 then copy down as needed. Biff "caldog" wrote in message ... The answer to your question is yes it does. Example: J. Brown, J. Simpson O. Holmes, T. Johns S. Oliver, K. Martin "T. Valko" wrote: Text to Columns is the best solution......... Using formulas can be somewhat complicated depending on how many commas may be present. Does every entry have the same number of commas? Post several representative samples. Biff "caldog" wrote in message ... Guess I will have to see if I can find another source for the information that I'm seeking. So far it is not forth coming here. "Don Guillett" wrote: Just select ALL of them at once and then do it... -- Don Guillett SalesAid Software "caldog" wrote in message ... Yes I know about that and it works, but it is time consuming, when I have more then one line to do. If I had formula, then it would happen automaticlly for me. "Don Guillett" wrote: Merry Xmas Have you tried datatext to columnspretty self explanatory -- Don Guillett SalesAid Software "caldog" wrote in message ... What I am trying to do is find a formula, that will locate the first comma in a text string, and then the next, until there is no more. Becasue I have some text information, that is seprated by commas, and I now need to put this information in different cells. BTW this information has been download from the interent, so somebody is just not keying this stuff in. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| Changing a comma separated text file and save it. | Excel Discussion (Misc queries) | |||
| Formula for adding a comma in front of text in a cell | Excel Worksheet Functions | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
