Delete text after delimiter using text to columns

[mrmcoupe]

Hi,

quite often I get spreadsheets containing product numbers - however, they often have a "#" as delimiter followed by a three letter option code. The data can come in a any column and vary from 5 to 1000 rows. So, I wan to make a permanent tool where I can select the column with the specific data - push the macro button (in my own tool view in the ribbon) and remove (delete) everything after the delimiter.

I have recorded a macro. However, the selection is static. I have also tried using various selection methods, but it always strands at the beginning of the texttocolumns code.

The error message I get is: Compile Error: Expected Function or Variable (highlighting "Selection" in the code). As if it doesn't have a selection to work with(?).
Also: not sure if the data in "Field Info" is correct. I guess this also should be dynamic.

I appreciate all the help I can get.

Sub remove_option_code()'


Dim rng As Range
Set rng = ActiveSheet.Range.CurrentRegion




'
'
    rng.Select
    Selection.TextToColumns _
    Destination:=rng, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=True, _
    OtherChar:="#", _
    FieldInfo:=Array(Array(1, 1), Array(2, 9)), _
    TrailingMinusNumbers:=True
    
    
End Sub

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

I think you maybe are making this more complicated than it needs to be

 

[steve the fish]

See if this works:

Dim rng As Range, x As Variant, lr as Long

Set rng = ActiveCell.Cells(1)
lr = Cells(Rows.Count, rng.Column).End(xlUp).Row
 
Set x = Range(Cells(1, rng.Column), Cells(lr, rng.Column))
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(LEFT(SUBSTITUTE(" & x.Address & ",""#"",REPT("" "",99)),99)))")
Range(Cells(1, rng.Column), Cells(lr, rng.Column)) = x

 

[mrmcoupe]

try posting a sample of your data, and what you would like to do with it

I think you maybe are making this more complicated than it needs to be

Sample data:

I would like to delete everything after #. Keeping the data in the same cells.

Y8Q66EA#ABN

1EN16EA#ABN

Y6J44EA#ABN

1EN21EA#ABN

X2F25EA#ABN

<tbody>
</tbody>

 

[FormR]

Hi, here is another option you could try:

Sub RemoveHashandAfter()
Selection.Replace What:="#*", Replacement:="", LookAt:=xlPart
End Sub

 

[mrmcoupe]

Hi! thanks for your solution.

I still get the same error message - Compile error - Expected funtion or variable.

brg
H

 

[mrmcoupe]

POW! And there it was! This one worked as expected! Thanks for you answer!

H

 

[mrmcoupe]

See if this works:

Dim rng As Range, x As Variant, lr as Long

Set rng = ActiveCell.Cells(1)
lr = Cells(Rows.Count, rng.Column).End(xlUp).Row
 
Set x = Range(Cells(1, rng.Column), Cells(lr, rng.Column))
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(LEFT(SUBSTITUTE(" & x.Address & ",""#"",REPT("" "",99)),99)))")
Range(Cells(1, rng.Column), Cells(lr, rng.Column)) = x

POW! And there it was! This one worked as expected! Thanks for you answer!

H

 

[FormR]

Compile error - Expected funtion or variable.

Hi, if you go Tools : References in the VBE are any marked as "Missing"?