Extract only part of a string

A

andreas

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
 
R

Rick Rothstein

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))
 
R

ryguy7272

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---
 
A

andreas

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 said:
If you had the string in cell A1:

"andreas" wrote:

- Show quoted text -

Hi, thank you very much for your professional help. Great Stuff.
Regards, Andreas
 
A

andreas

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)










- 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
 
A

andreas

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)










- Zitierten Text anzeigen -

Hi Rick,

Ok, I got it working. Thank you very much for your professional help.
Regards, Andreas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top