Extract Word starting from first letter

[nicnad]

Hi,


I have cells that contains :

1234-5678-Barrack Obama
4567-7655-124678-Ben Bernanke


How do I extract the text string starting from the first letter? (i.e Barrack Obama, Ben Bernanke)

Thank you.

 

[pgc01]

Hi

Assuming the name is always after the last hyphen, like in the examples, try:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

 

[Michael M]

Hey PGC
I was going to throw in my first Regex solution !!

 

[aiwnjoo]

Or;

=RIGHT(A1,FIND("-",A1,1)-1)

?

 

[pgc01]

Hey PGC
I was going to throw in my first Regex solution !!

Hi Michael

... and you still may have to, if the real data is more complex that the examples.

 

[Biz]

Not sure how give alphabets using RegExp on its own but used substitute formula with it. Is there a better way to give alphabets?

Still learning.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 234px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234-5678-Barrack Obama</TD><TD style="BACKGROUND-COLOR: #99cc00">Barrack Obama</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4567-7655-124678-Ben Bernanke</TD><TD style="BACKGROUND-COLOR: #99cc00">Ben Bernanke</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=SUBSTITUTE(AlphaNum(A1,TRUE),"-","")</TD></TR><TR><TD>B2</TD><TD>=SUBSTITUTE(AlphaNum(A2,TRUE),"-","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) As String
'Use in cell like _
=AlphaNum(A1,True) '<- True for Alphabets, False for Numbers
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(Alpha, "\d+", "\D+")
    .Global = True
    AlphaNum = .Replace(txt, "")
End With
End Function

 

[Michael M]

My first crack at Regex ( with PGC's base code) was:

Function UC(s As String) As String 'Finds Name with Proper Case
'user must activate Microsoft vbscript regular expressions 5.5 in Tools / References
Application.Volatile
With CreateObject("VBScript.RegExp")
  .Pattern = "(\b[A-Z]\w*\s\b[A-Z]\w*)"
  If .Test(s) Then UC = .Execute(s)(0).SubMatches(0)
End With
End Function

then use

=UC(A1)

 

[nicnad]

Thank you very much. Biz formula did the trick.

 

[nicnad]

You guys seems to know a lot about Excel. Could you have a look at my post at : http://www.mrexcel.com/forum/showthread.php?p=2879444#post2879444

Thank you for your time and help!

 

[Biz]

My first crack at Regex ( with PGC's base code) was:

Function UC(s As String) As String 'Finds Name with Proper Case
'user must activate Microsoft vbscript regular expressions 5.5 in Tools / References
Application.Volatile
With CreateObject("VBScript.RegExp")
  .Pattern = "(\b[A-Z]\w*\s\b[A-Z]\w*)"
  If .Test(s) Then UC = .Execute(s)(0).SubMatches(0)
End With
End Function

then use

=UC(A1)

Nice 1 m8.

Biz