Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Hello everyone ! My first steps in this *nice* forum :-) here is my question : I got in C37 cell this: Year began: 1965 Franchising since: 1974 Basically i'm importing this from the web and I will always have in this cell this text, with this format, though dates will change. I would like to write a macro that will enter this cell, copy/paste the first date, here 1965 in cell C14 and then copy/paste the second date in cell C15. When I use the recorder, It just copies twice in C14 and C15 the last date, 1974. How can I do that ? Here is my script: Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C14").Select ActiveSheet.Paste Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C15").Select ActiveSheet.Paste -- crazy_vba ------------------------------------------------------------------------ crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Hi,
range("c15").value=val(right(range("c37").value,4) ) range("c14").value=val(mid(range("c37").value,13,4 ) instead of the code you posted. Regards, Ivan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Dim iPos As Long
Dim tmp As String tmp = Range("C37").Value iPos = InStr(tmp, ":") If iPos = 0 Then MsgBox "Error in data" Else Range("C14").Value = Mid(tmp, iPos + 2, 4) iPos = InStr(iPos + 1, tmp, ":") If iPos = 0 Then MsgBox "Error in data" Else Range("C15").Value = Mid(tmp, iPos + 2, 4) End If End If -- HTH Bob Phillips (remove nothere from email address if mailing direct) "crazy_vba" wrote in message ... Hello everyone ! My first steps in this *nice* forum :-) here is my question : I got in C37 cell this: Year began: 1965 Franchising since: 1974 Basically i'm importing this from the web and I will always have in this cell this text, with this format, though dates will change. I would like to write a macro that will enter this cell, copy/paste the first date, here 1965 in cell C14 and then copy/paste the second date in cell C15. When I use the recorder, It just copies twice in C14 and C15 the last date, 1974. How can I do that ? Here is my script: Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C14").Select ActiveSheet.Paste Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C15").Select ActiveSheet.Paste -- crazy_vba ------------------------------------------------------------------------ crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
in C13: =TRIM(MID($C$37,FIND(":",$C$37,1)+1,6)) in C14 =TRIM(RIGHT($C$37,LEN(C37)-FIND("ce:",$C$37,1)-3)) or if year is last 4 characters then =RIGHTt($C$37,4) HTH "crazy_vba" wrote: Hello everyone ! My first steps in this *nice* forum :-) here is my question : I got in C37 cell this: Year began: 1965 Franchising since: 1974 Basically i'm importing this from the web and I will always have in this cell this text, with this format, though dates will change. I would like to write a macro that will enter this cell, copy/paste the first date, here 1965 in cell C14 and then copy/paste the second date in cell C15. When I use the recorder, It just copies twice in C14 and C15 the last date, 1974. How can I do that ? Here is my script: Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C14").Select ActiveSheet.Paste Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C15").Select ActiveSheet.Paste -- crazy_vba ------------------------------------------------------------------------ crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Hi, Starting with the macro recorder is a good approach, however the most (or a more) effective way of doing this can not be replicated using the recorder because a lot can be done without selecting. If the layout (ie spacing) of the cell string will always be the same, try: Sub InsertYears() Dim OriginalString As String OriginalString = Range("C37").Value Range("C14").Value = Mid(OriginalString, 13, 4) Range("C15").Value = Right(OriginalString, 4) End Sub which can usually be shortened (provided excel considers ".value" as the default) to: Sub InsertYears() Dim OriginalString As String OriginalString = Range("C37") Range("C14") = Mid(OriginalString, 13, 4) Range("C15") = Right(OriginalString, 4) End Sub hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
On Thu, 20 Apr 2006 06:29:35 -0500, crazy_vba
wrote: Hello everyone ! My first steps in this *nice* forum :-) here is my question : I got in C37 cell this: Year began: 1965 Franchising since: 1974 Basically i'm importing this from the web and I will always have in this cell this text, with this format, though dates will change. I would like to write a macro that will enter this cell, copy/paste the first date, here 1965 in cell C14 and then copy/paste the second date in cell C15. When I use the recorder, It just copies twice in C14 and C15 the last date, 1974. How can I do that ? Here is my script: Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C14").Select ActiveSheet.Paste Range("C37").Select ActiveCell.FormulaR1C1 = "Year began: 1965 Franchising since: 1974" Range("C15").Select ActiveSheet.Paste For a macro: ======================= Sub ExtractDates() Dim Target As Range Dim Res As Range Dim i As Long, j As Long Dim yr As Long Set Target = [C37] Set Res = [C14] For j = 0 To 1 Do Until yr 0 yr = Val(Right(Target, Len(Target) - i)) i = i + 1 Loop i = i + Len(yr) Res.Offset(j, 0).Value = yr yr = 0 Next j End Sub ========================== Or, you could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr and then use the Regular expression formulas: C14: =REGEX.MID(C37,"\d+") C15: =REGEX.MID(C37,"\d+",2) --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Whao guys ! A.M.A.Z.I.N.G. ! I was not expected to have so many helpers out there. For sure It helped me and I want to thank y'all for your help / support ! I hope that one day I'll be able to help newbies like the one I am for now ! Thanks again Crazy Vba -- crazy_vba ------------------------------------------------------------------------ crazy_vba's Profile: http://www.excelforum.com/member.php...o&userid=33679 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get specific strings out of a cell ?
Hi, No problem, thanks for the feedback, pleased we could help :-) Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=534508 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Extracting specific data from Date and time strings | Excel Discussion (Misc queries) | |||
how do i count specific text strings excluding blank cells? | Excel Worksheet Functions | |||
using excel text functions (specific to strings) | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions |