how to choose a part of the text in a cell???

K

Ksenija

Can someone help me? If I have a text saying "AAAAA_BBBBB" in a cell, how can
I write a code that copies the text in front of "_" and pastes it in a cell
above????

So I have "AAAAA_BBBBB" and want to have AAAAA pasted in a cell above..
 
J

Jacob Skaria

If you are looking for a fomula try the below
=LEFT(A1,FIND("_",A1)-1)

If you are looking for a VBA code; try

Split(Range("A1"),"_")(0)

or

Dim strData as String
strData = "AAAAA_BBBBB"
Msgbox Split(strData,"_")(0)
 
K

Ksenija

Hi again,
sorry, I can't make it work...

the thing is, I am trying to put this in a loop så it goes thrue a lot of
cells, så I can't specify which exact cell I am in (A1 etc)..

so I don't really knom how to make

Split(Range("A1"),"_")(0),

work...
 
R

Rick Rothstein

It would really help us if you posted the code you have so we can see how to
integrate this into it.
 
F

fisch4bill

Hi, assuming that your data is in A2: ??2 and that you want it pasted in A1:
??1, try the following (adjusted to fit your data's location):

Sub TextSplit()
Dim Cell As Range
For Each Cell In Range("A2:" & Cells(2, Columns.Count).End(xlToLeft).Address)
Cell.Offset(-1, 0).Value = Split(Cell.Value, "_")(0)
Next Cell
End Sub

Hope this helps, but without seeing more detail (your code would be helpful)
I can't be more specific. This code will select just the data to the left of
the "_". If you want the data to the right ("BBBBB" from "AAAAA_BBBBB") then
substitute a 1 for the 0.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top