how to get specific strings out of a cell ?

[crazy_vba]

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

 

[Ivan Raiminius]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[broro183]

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

 

[Ron Rosenfeld]

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

 

[crazy_vba]

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

 

[broro183]

Hi,
No problem, thanks for the feedback, pleased we could help

Rob Brockett
NZ
Always learning & the best way to learn is to experience...