Solved: Remove first word in a string

[markmrw]

Hiya Ladies and gents

I need to find a way to remove the first word from a string.
using VBA as the range is not always the same. (so i can't use a worksheet formula)

The strings are not always the same length
i.e.

Length WHP 111
Height WHP 222
Depth WHP 333

Can the first word be trimmed off, i don't have to keep the first word so it can just be deleted.
i have tried using Left (But im a bit thick) :)

Any Help would be greatly appreciated

Mark

[mdmackillop]

Sub DelLeft()
Dim l as long, d as long
l = Len(Selection)
d = InStr(1, Selection, " ")
If Not d = 0 Then Selection.Offset(, 1) = Right(Selection, l - d)
End Sub

[Bob Phillips]

Hiya Ladies and gents

I need to find a way to remove the first word from a string.
using VBA as the range is not always the same. (so i can't use a worksheet formula)

Yes you can,

=MID(A1,FIND(" ",A1)+1,255)

[markmrw]

Perfect thank you md

as a matter of interest
what is As Long?

[mdmackillop]

Long is a Data Type

From VBA Help
Using Data Types Efficiently


Unless otherwise specified, undeclared variables (http://javascript<b></b>:hhobj_3.Click()) are assigned the Variant data type (http://javascript<b></b>:hhobj_4.Click()). This data type makes it easy to write programs, but it is not always the most efficient data type to use.

You should consider using other data types if:

<LI class=LB1>Your program is very large and uses many variables.

<LI class=LB1>Your program must run as quickly as possible.
You write data directly to random-access files.In addition to Variant, supported data types include Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, and String. Use the Dim statement to declare a variable of a specific type, for example:

[markmrw]

Thank you Very much the code works lovely!

:)

[markmrw]

i cannot "mark as solved" ????

[mdmackillop]

You do this using the Thread Tools dropdown.

[markmrw]

i use google chrome

its does not have the drop down.

[Bob Phillips]

It is done.

[diego80]

Sub DelLeft()
Dim l as long, d as long
l = Len(Selection)
d = InStr(1, Selection, " ")
If Not d = 0 Then Selection.Offset(, 1) = Right(Selection, l - d)
End Sub



hi there,

I noticed that this code does not work with a multiple cells selection.

Is it something I made wrong or is it just the code that wasn't conceived for that purpose?

I made a small clip for a better demonstration

https://youtu.be/XTkxx6b_SUA

and by the way:
could it be possible to request a custom edit so that the output is overwritten within the same cells? :)

producing a second column would be a bit redundant for the usage I am looking at.

Thanks for the code anyway: it's probably one of the few you can find on line.

[mdmackillop]

Sub DelLeft()
Dim l As Long, d As Long
Dim Cel As Range
For Each Cel In Selection
l = Len(Cel)
d = InStr(1, Cel, " ")
'If Not d = 0 Then cel.Offset(, 1).Value = Right(cel, l - d)
'or
If Not d = 0 Then Cel.Value = Right(Cel, l - d)
Next Cel
End Sub

[diego80]

thanks man: I'll try right away!!

[diego80]

thanks very much,

it works perfectly:

https://youtu.be/kT6YBVgu2cA