Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
Dear Experts:
I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
If you had the string in cell A1: =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))) "andreas" wrote: Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
On the off change that some of your text could contain more than one comma,
like for instance... 123 Main St, Apt 4, New York 56789 then you can use this formula to get the text that you want... =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) -- Rick (MVP - Excel) "andreas" wrote in message ... Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
This works for me:
Sub Commas() 'Do Until ActiveCell = "" For X = 1 To 1 Dim Rng As Range Set Rng = Range("A1", Range("A100").End(xlUp)) For Each cell In Rng If cell.Value < "" Then ActiveCell.Offset(0, 2).Select ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND("","",RC[-2]&"","")+2,999)" ActiveCell.Offset(1, -2).Select End If Next cell Next X 'Loop End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sam Wilson" wrote: If you had the string in cell A1: =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))) "andreas" wrote: Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
On Jul 20, 6:24*pm, ryguy7272
wrote: This works for me: Sub Commas() 'Do Until ActiveCell = "" For X = 1 To 1 * * Dim Rng As Range * * Set Rng = Range("A1", Range("A100").End(xlUp)) * * For Each cell In Rng * * If cell.Value < "" Then * * * * ActiveCell.Offset(0, 2).Select * * * * ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND("","",RC[-2]&"","")+2,999)" * * * * ActiveCell.Offset(1, -2).Select * * End If * * Next cell Next X 'Loop End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.. "Sam Wilson" wrote: If you had the string in cell A1: =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))) "andreas" wrote: Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas- Hide quoted text - - Show quoted text - Hi, thank you very much for your professional help. Great Stuff. Regards, Andreas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
On Jul 20, 6:14*pm, "Rick Rothstein"
wrote: On the off change that some of your text could contain more than one comma, like for instance... 123 Main St, Apt 4, New York 56789 then you can use this formula to get the text that you want... =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) -- Rick (MVP - Excel) "andreas" wrote in message ... Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas- Hide quoted text - - Show quoted text - Hi Rick, thank you very much for your professional help. I tried it out and I keep getting error messages. I have translated the function arguments into German (having the german version of Excel). Maybe therefore the errors. I will keep you updated. Regards, Andreas |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract only part of a string
On 20 Jul., 18:14, "Rick Rothstein"
wrote: On the off change that some of your text could contain more than one comma, like for instance... 123 Main St, Apt 4, New York 56789 then you can use this formula to get the text that you want... =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) -- Rick (MVP - Excel) "andreas" wrote in message ... Dear Experts: I got the following string in a cell: string,space,string, e.g.: New York Street 7, New York 54334 or Green Street, 74334 St. Petersburg. I would like to apply a formula to all these cells so that all the characters before the comma are cut out and only the string after the comma is left over, e.g. New York 54334 or 74334 St. Petersburg Help is much appreciated. Thank you very much in advance. Regards, Andreas- Zitierten Text ausblenden - - Zitierten Text anzeigen - Hi Rick, Ok, I got it working. Thank you very much for your professional help. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
Extract part of a text string | Excel Worksheet Functions | |||
How do I extract part of a text string | Excel Discussion (Misc queries) | |||
Extract part of a Worksheet name. | Excel Programming | |||
Extract Part of String | Excel Worksheet Functions |