Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extracting strings

  Asked By: Bogart    Date: Feb 09    Category: MS Office    Views: 1330
  

I want to extract some text (say "town1", "town2" etc,) from a column,
say (A1;A55) with 55 text lines, (can b e longer or shorter), using
Strings Right (text required comes after "location," , and place in
the next avaiable row in a column. Say column K.

Then continue selecting until end of used cells in Column A, and then
return to top of column A for another set of instructions.

Share: 

 

12 Answers Found

 
Answer #1    Answered By: Ludo Ricci     Answered On: Feb 09

Can you show us what you have done so far? I am not going to write all
your code for you.

 
Answer #2    Answered By: Luigi Fischer     Answered On: Feb 09

i think the best possible way would be to use Find
function along with mid, and replace if necessary.

Please venture out with find funciton and mid.

example

Say i have the following text  in A1
afasdgp;oap location: anand1 werfasdf

I want to Extract anand1 alone, i use the following
formula,
=LEFT(TRIM(MID(A1,FIND("location:",A1,1)+9,LEN(A1))),FIND("
",TRIM(MID(A1,FIND("location:",A1,1)+9,LEN(A1))),1))

 
Answer #3    Answered By: Latasha Wilson     Answered On: Feb 09

This is my first go at VBA so still
getting to know the ropes. The brain is 63 years old

Below is my first go at this exercise. Still not sure on the loop
actions however.

If you can give me some hints as to the correct action and I will
try!! and get it together.


Private Sub Extract Text_Click()
st1= "location,"
Range ("A1").Select
Do
cells ( ,9) = Right (St1, )
Do
loop until IsEmpty=true
cells


Do I need to put any Dim statement? Boolean?
The text  (after location,) can vary in length
Entry of town is in the next available cell
should the second part of instructions be inserted into first
Needs some closing codes.


Where is a good place  to find out entries to open instructions, and
to end  instructions.
I did say  I was a learner.

 
Answer #4    Answered By: Ora Hanson     Answered On: Feb 09

: Thanks for your interest. This is my first go at VBA so
: still getting to know the ropes. The brain is 63 years
: old
:
: Below is my first go at this exercise. Still not sure on
: the loop actions however.
:
: If you can give me some hints as to the correct action and I
: will try!! and get it together.

If you are building a car engine from scratch, you wouldn't
just throw the parts into a box and expect the engine to run.
You have to learn which parts fit together, test those newly
formed sections and then learn how those sections connect and
test those new connections until you end  up with a working
engine.

Writing computer programs is no different. You can't just
throw words in a file and expect a finished program to pop out.
You have to start with a simple project and work your way up.
When learning a new language, one of the most common tasks is
the "Hello World" program. Start with this tutorial.

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

It will show you some basics. (I picked it out from a
Google search.) Learn some basics and then bite off a bigger
project like this one.

 
Answer #5    Answered By: Angel Watkins     Answered On: Feb 09

Never to old to learn, Charlie. Welcome to the wonderful, powerful world
of VBA.


I tackled what you want to do both in Excel, and in VBA. Before we get
to may answers, and lack thereof, let's discuss resources:

groups.google.com/advanced_group_search?q=group:*Excel*&num=100
is my other main resource apart from this group.
A lot of things you want to do will also be covered by
www.vba-programmer.com, e.g. determining last row  of data.
Another great resource is http://www.mvps.org/dmcritchie/excel/excel.htm
for many different reasons.

A great place  to start learning about VBA for Excel is
web.archive.org/.../default.
aspx?scid=/support/excel/content/vba101/default.asp. (See the ritchie
pages for more links).

------------------------------------------------------------------------
-----------------------------------------------------------------
My VBA code:

Option Explicit

Sub Extract_Text_Click()

' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long

' Determine number of rows in user selected column  to loop through.
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count

' Start looping through column A

For i = 1 To Data_rows

' Extract the text, and paste into column K using offset
function - Below not tested. Should be easy to make it work, though.
Range("K" & i).Value = Right(Range("A" & i), Find("location, ",
Range("A" & i), 2) + 20).Value

Next i

End Sub

 
Answer #6    Answered By: Burkett Bernard     Answered On: Feb 09

Thanks very much for all the info on Excel/VBA and the code. Magic.

I will swat up on it and try it out in the next few days.

 
Answer #7    Answered By: Perdita Lopez     Answered On: Feb 09

I have sorted a couple of minor syntax problems in the VBA code, but
now when I press the comand button nothing results. Not even a
message to say  there is a problem or any text  extracted into column
k.

Syntax problems were a ' required  and a _ required.

I can folow the code as to what it does up until near the end  and I
havent worked out what the 2)+20) does ? Can you give me an
insight into that.

Can you give me a guidence to sort.

I have listed the data (3 sets) to be used and the code I am using
below.

6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS
Terminal Shipping Burl North 460020
Terminal Shipping Sante Fe 100396
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS
GRAINCAR
GRAINCAR
empty
empty
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Burl North
Sante Fe
460020
100396
GRAINCAR
GRAINCAR
Grn w/Wht Lttrs -4 Bay
Maroon w/Wht Ltr -3 Bay
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -East
DEPARTURE TIME from WESTTOWN is 01:10
TOWN STOP---BUSNES TWN Arriving at 01:40
PICKUPS
Wholsale Co. Ltd. Candn Natl 78358 BOXCAR load Downtown Mall
Train should leave this town with 1 car(s)
TOWN STOP---MFG TWN Arriving at 02:10
PICKUPS
Furniture Factory Grand Trunk 3780
Train should leave this town with 2 car(s)
TOWN STOP---SHOPNG TWN Arriving at 03:10
SETOUTS
LONGBOX load Downtown Mall
Downtown Mall
Downtown Mall
Grand Trunk
Candn Natl
3780
78358
LONGBOX
BOXCAR
82' Blu w/Wht Lttr
40' Org w/Gld Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Easttwn Wayfrght
DEPARTURE TIME from EASTTOWN is 01:20
TOWN STOP---SHOPNG TWN Arriving at 01:50
PICKUPS
Downtown Mall CN RailBox 098 BOXCAR empty Wholsale Co. Ltd.
Downtown Mall Sante Fe 20634 BOXCAR empty Wholsale Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---BUSNES TWN Arriving at 02:50
SETOUTS
Wholsale Co. Ltd. CN RailBox 098 BOXCAR 40' Ylw w/Blk Lttr
Wholsale Co. Ltd. Sante Fe 20634 BOXCAR 40' Brn w/Wht Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -West
DEPARTURE TIME from EASTTOWN is 01:25
PICKUPS
East Freight Yard Candn Govt 106200 GRAINCAR load Terminal Shipping
Train should leave this town with 1 car(s)
TOWN STOP---SEAPRT TWN Arriving at 03:55
SETOUTS
Terminal Shipping Candn Govt 106200 GRAINCAR Ylw & Sil w/Red Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS
Terminal Shipping Burl North 460020
Terminal Shipping Sante Fe 100396
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS
GRAINCAR
GRAINCAR
empty
empty
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Elevator Co. Ltd.
Burl North
Sante Fe
460020
100396
GRAINCAR
GRAINCAR
Grn w/Wht Lttrs -4 Bay
Maroon w/Wht Ltr -3 Bay
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -East
DEPARTURE TIME from WESTTOWN is 01:10
TOWN STOP---BUSNES TWN Arriving at 01:40
PICKUPS
Wholsale Co. Ltd. Candn Natl 78358 BOXCAR load Downtown Mall
Train should leave this town with 1 car(s)
TOWN STOP---MFG TWN Arriving at 02:10
PICKUPS
Furniture Factory Grand Trunk 3780
Train should leave this town with 2 car(s)
TOWN STOP---SHOPNG TWN Arriving at 03:10
SETOUTS
LONGBOX load Downtown Mall
Downtown Mall
Downtown Mall
Grand Trunk
Candn Natl
3780
78358
LONGBOX
BOXCAR
82' Blu w/Wht Lttr
40' Org w/Gld Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Easttwn Wayfrght
DEPARTURE TIME from EASTTOWN is 01:20
TOWN STOP---SHOPNG TWN Arriving at 01:50
PICKUPS
Downtown Mall CN RailBox 098 BOXCAR empty Wholsale Co. Ltd.
Downtown Mall Sante Fe 20634 BOXCAR empty Wholsale Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---BUSNES TWN Arriving at 02:50
SETOUTS
Wholsale Co. Ltd. CN RailBox 098 BOXCAR 40' Ylw w/Blk Lttr
Wholsale Co. Ltd. Sante Fe 20634 BOXCAR 40' Brn w/Wht Lttr
6/01/2007 8:39:08 p.m.
SWITCHLIST FOR TRAIN---Genrl Frght -West
DEPARTURE TIME from EASTTOWN is 01:25
PICKUPS
East Freight Yard Candn Govt 106200 GRAINCAR load Terminal Shipping
Train should leave this town with 1 car(s)
TOWN STOP---SEAPRT TWN Arriving at 03:55
SETOUTS
Terminal Shipping Candn Govt 106200 GRAINCAR Ylw & Sil w/Red Lttr

CODE

Option Explicit

Sub Extract_Text_Click()

' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long

' Determine number of rows in user selected column  to loop through.
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count

' Start looping through column A

For i = 1 To Data_rows

extract  the text, and paste into column K using offset
' function - Below not tested. Should be easy to make it work,
though.
Range("K" & i).Value = Right(Range("A" & i), Find("STOP---", _
Range("A" & i), 2) + 20).Value

Next i


End Sub

 
Answer #8    Answered By: Faith Hughes     Answered On: Feb 09

Now, seeing the dataset you want to process, I would approach it very
differently:
1) As before, determine the number of rows to process
2) For each line in the rows, if starts with "TOWN STOP---", then
copy to column  k
Replace "TOWN STOP---" with ""
Else
End If

Now, since I do not know the correct format for the Replace function, I
am going to record a macro to do that.

ActiveCell.Replace What:="TOWN STOP---", Replacement:="", LookAt:=xlPart
_
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Activecell is not appropriate. We will replace that with a Range()
statement later on.

------------------------------------------------------------------------
-
Using Google Group Search, I searched for "text contains" and found that
I could use "InStr" as a command to test whether "Town Stop---" is in
the string or not.

Code now looks as follows:

Sub Extract_Text_Click()

' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
Dim Searchstring, Searchchar, MyPos

' Determine number of rows in user selected column to loop through.
' First, select column A
Range("A1").Select
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count

' Start looping through column A

For i = 1 To Data_rows

Searchchar = "TOWN STOP---"
Range("A1").Offset(i, 0).Select
Searchstring = ActiveCell.Value
MyPos = InStr(1, Searchstring, Searchchar, 1)
Debug.Print Searchstring, "|||", Searchchar, "|||", MyPos
Next i

End Sub
-----------------------------------
The debug.print shows me in the Immediate Window (In VB Editor, select
from View menu if not visible) that MyPos is zero, UNLESS "Town Stop---"
is present.

Cool. Now we can say: If MyPos = 1 then
' copy to column k
' Replace "TOWN STOP---" with ""
Else
'Do nothing
End If
-------------------------------------------------------------- Which
gives the final code of------------------------------
Option Explicit


Sub Extract_Text_Click()

' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
Dim Searchstring, Searchchar, MyPos

' Determine number of rows in user selected column to loop through.
' First, select column A
Range("A1").Select
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count

' Start looping through column A

For i = 1 To Data_rows

Searchchar = "TOWN STOP---"
Range("A1").Offset(i, 0).Select
Searchstring = ActiveCell.Value
MyPos = InStr(1, Searchstring, Searchchar, 1)
Debug.Print Searchstring, "|||", Searchchar, "|||", MyPos

If MyPos = 1 Then Range("k" & i).Value = Searchstring
' We now have the value in column K, now we need to remove the
Town Stop bit.
Range("k" & i).Replace What:="TOWN STOP---", Replacement:="",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
'Lastly, If you just want the town name, I see that the string "Arriving
at" is also
'present in all instances. Using "Left" or RIGHT" and another InStr to
dedermine where
' "Arriving at" starts, you can delete that programmatically as well.
'
' Code executes very quickly. If you want to delete/hide all rows not
containing values
' in column K, see one of the resources I mentioned earlier.

 
Answer #9    Answered By: Dinh Tran     Answered On: Feb 09

I have set  it up but when I run it nothing
happens not even an error note etc. I feel the code is right is it
possible that I have formatting or something not set correctly?

I will come back on the time part, yes that will have to be deleted.

Code used:

Option Explicit

Sub Extract_Text_Click()

' Define variables
Dim St1 As Variant
Dim i As Long
Dim Data_rows As Long
Dim Searchstring, Searchchar, MyPos

' Determine number of rows in user selected column  to loop through.
' First, select column A
Range("A1").Select
Data_rows = Range(Selection, Selection.End(xlDown)).Rows.Count

' Start looping through column A

For i = 1 To Data_rows

Searchchar = "TOWN STOP---"
Range("A1").Offset(i, 0).Select
Searchstring = ActiveCell.Value
MyPos = InStr(1, Searchstring, Searchchar, 1)
Debug.Print Searchstring, "|||", Searchchar, "|||", MyPos

If MyPos = 1 Then Range("k" & i).Value = Searchstring

Range("k" & i).Replace What:="TOWN STOP---", Replacement:="", _
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next i

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

 
Answer #10    Answered By: Ann Evans     Answered On: Feb 09

Code works for me on the dataset, with the dataset in column  A. Try
stepping through the code using the F8 key in the VBE, and see what the
immediate window tells you.

 
Answer #11    Answered By: Dan Romero     Answered On: Feb 09

I was a bit rushed this morning at work, and missed your
question. Not unlike now, when I want to go to bed!

To find out what the 2 and 20 does, you have to set  up the formulas in
Excel, and play with them. Based on my earlier post, you will need to do
that anyway.

Excel Help covers the RIGHT function in detail, both in the workbook
environment, and in the VBE.

Which is my long way of saying - I don't have a clue how RIGHT works,
but I know where to find help - which in Excel, is perhaps more
important!

 
Answer #12    Answered By: Clint Garcia     Answered On: Feb 09

Do you need code? Surely a formula in K1:K55 would do this for you?

 
Didn't find what you were looking for? Find more on Extracting strings Or get search suggestion and latest updates.




Tagged: