fpoyavo
asked on
Validate Excel via VBA
Hi Experts,
I have excel file with one spreadsheet and one column and in this column I have data.
Some of the cells have in different places substring " - abc .... " which can be a different length. What I would do is to find this substring and whatever is after it till the end of line
copy and paste in another cell and then remove from original cell.
so basically here is example :
Input :
A
aaaaaaaa - abc 012345
bbbbbbb - abc 01282319274
cccccccc - abc 0112
Output :
A B
aaaaaaaa - abc 012345
bbbbbbb - abc 01282319274
cccccccc - abc 0112
Thanks.
I have excel file with one spreadsheet and one column and in this column I have data.
Some of the cells have in different places substring " - abc .... " which can be a different length. What I would do is to find this substring and whatever is after it till the end of line
copy and paste in another cell and then remove from original cell.
so basically here is example :
Input :
A
aaaaaaaa - abc 012345
bbbbbbb - abc 01282319274
cccccccc - abc 0112
Output :
A B
aaaaaaaa - abc 012345
bbbbbbb - abc 01282319274
cccccccc - abc 0112
Thanks.
ASKER
Not sure if you understood the specs. I didn't ask to hide anything. I needed To find and copy to another cell and then to remove it from original cell.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also use the "Text to Columns" function.
Select the column of data and then on the Data tab click the Text to Columns button.
Step 1 - select Delimited, click OK
Step 2 - check the boxes next to Space and Other and next to Other type - in the box. AT the top right check the box for treating consecutive delimiters as one
Step 3 - Click Finish
This will split the data into two columns.
Thanks
Rob H
Select the column of data and then on the Data tab click the Text to Columns button.
Step 1 - select Delimited, click OK
Step 2 - check the boxes next to Space and Other and next to Other type - in the box. AT the top right check the box for treating consecutive delimiters as one
Step 3 - Click Finish
This will split the data into two columns.
Thanks
Rob H
ASKER
Hi Rob,
It will not work since not all cells have this " - abc ..."
Thanks.
It will not work since not all cells have this " - abc ..."
Thanks.
So those that do not have the "- abc" will be left as they are. Would that be the result you want anyway?
Thanks
Rob
Thanks
Rob
ASKER
Hi Flyster,
I t gets where " - abc ..." correctly but where I don't have " - abc .." it should not remove in column A anything ... now it does leaves A empty and copies this data into B.
Thanks.
I t gets where " - abc ..." correctly but where I don't have " - abc .." it should not remove in column A anything ... now it does leaves A empty and copies this data into B.
Thanks.
Try this one. It will copy only the cells that contains "- abc", the rest will be left untouched:
Sub SplitString()
Dim stra, strb As String
Dim i As Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If InStr(1, Range("A" & i), "- abc") <> 0 Then
stra = Left(Range("A" & i), InStr(Range("A" & i), " "))
strb = Right(Range("A" & i), Len(Range("A" & i)) - InStr(Range("A" & i), " "))
Range("B" & i).Value = strb
Range("A" & i).Value = stra
End If
Next i
End Sub
=LEFT(A1,FIND(" ",A1))
And this will give you the second part:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
You can then hide column A.
Flyster