View Full Version : [SOLVED:] Extract a string from a cell
YasserKhalil
07-23-2010, 01:38 AM
Hi guys
I've a string of characters and numbers and there's a string which I need to extract
This string is between two symbols < .......... >
for example :
my e-mail is <yakh777@yahoo.com> please send me soon
This is the whole string .. My result should be : yakh777@yahoo.com
Thanks advanced for help
Bob Phillips
07-23-2010, 02:46 AM
Try
=MID(A2,FIND("<",A2)+1,FIND(">",A2)-FIND("<",A2)-1)
YasserKhalil
07-23-2010, 02:59 AM
Perfect Mr. xld
I'm pleased with your solution
Can I ask you to perform this solution using VBA to be applied to the range("A1:A1000")??
As I think the formulas will make the size of the file (awful)
Bob Phillips
07-23-2010, 03:22 AM
Not on 1,000 cells it won't.
YasserKhalil
07-23-2010, 04:22 AM
In fact, the range is more than that
It may be 55000 or more!!!
I'm surprised also of this range but it's someone's problem and I want to help him
Bob Phillips
07-23-2010, 04:25 AM
With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2")
.Resize(LastRow - 1).Formula = "=MID(A2,FIND(""<"",A2)+1,FIND("">"",A2)-FIND(""<"",A2)-1)"
.Value = .Value
End With
End With
YasserKhalil
07-23-2010, 05:12 AM
Thank you very much Mr. xld
You are a great guy!
---------------------------
I made a simple change
Application.ScreenUpdating = False
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B2:B" & LastRow)
.Resize(LastRow - 1).Formula = "=MID(A2,FIND(""<"",A2)+1,FIND("">"",A2)-FIND(""<"",A2)-1)"
.Value = .Value
End With
End With
Application.ScreenUpdating = False
Bob Phillips
07-23-2010, 05:53 AM
Sorry, it should have been
With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2") .Resize(LastRow - 1)
.Formula = "=MID(A2,FIND(""<"",A2)+1,FIND("">"",A2)-FIND(""<"",A2)-1)"
.Value = .Value
End With
End With
YasserKhalil
07-23-2010, 10:57 AM
With .Range("A2") .Resize(LastRow - 1)
This line should be
With .Range("B2").Resize(LastRow - 1)
As I want the results to be in Column B
The code now is working perfect!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.