PDA

View Full Version : [SOLVED] HOW TO STRIP OUT ONLY RELEVANT DATA FROM A STRING BY USING FORMULA OR VBA



ghdub
09-28-2015, 08:37 AM
Hello, I have just recently got into VBA and am having difficulty getting the relevant data from the below text string as the length varies.
I am looking to get the bold text from the below strings

ie 9600, 24 3150,12975 copied into column B

Can somebody suggest a formula, or alternatively a vba code to strip out the relevant numbers bolded below and copy in the data into the next column.
There is a fixed character length at the right and left of the relevant data.

COLUMN A


Basis, Klasse ("OPT, 254856, DE0008469008, 846949, 20.06.2016, 9600, C") could not be determined



Basis, Klasse ("OPT, 254857, DE0005552004, 555200, 20.06.2016, 24, C") could not be determined



Basis, Klasse ("OPT, 254892, EU0009658145, 965239, 19.10.2015, 3150, C") could not be determined



Basis, Klasse ("OPT, 254893, EU0009658145, 965239, 19.10.2015, 12975, P") could not be determined

mancubus
09-28-2015, 01:27 PM
if the texts in col A are comma separated and the bit that will be extracted is the bit before last comma, try:



Sub vbax_53848_xtract_nums_comma_delimeter()

Dim i As Long
Dim CommaSplitted

With Worksheets("Sheet1") 'change Sheet1 to suit
For i = 2 To Cells(.Rows.Count, 1).End(xlUp).Row
CommaSplitted = Split(.Cells(i, 1).Value, ",")
.Cells(i, 2).Value = WorksheetFunction.Trim(CommaSplitted(UBound(CommaSplitted) - 1))
Next i
End With

End Sub

ghdub
09-29-2015, 06:00 AM
Many thanks mancubus, I really appreciate you help in this matter.
There is absolutely no way I would have figured that code out without your help.

I have one slight issue remaining, which you may be able to help with?

For some reason the code doesn't seem to work for the first 'string' in the column A, it only works for all subsequent rows.

Do you have any suggestions on how to correct this?

Results of code


Basis, Klasse ("OPT, 254856, DE0008469008, 846949, 20.06.2016, 9600, C") could not be determined



Basis, Klasse ("OPT, 254857, DE0005552004, 555200, 20.06.2016, 24, C") could not be determined
24


Basis, Klasse ("OPT, 254892, EU0009658145, 965239, 19.10.2015, 3150, C") could not be determined
3150


Basis, Klasse ("OPT, 254893, EU0009658145, 965239, 19.10.2015, 12975, P") could not be determined
12975

mancubus
09-29-2015, 06:41 AM
You are welcome.

I assumed cell A1 contained a column header...

Change
For i = 2
To
For i = 1

ghdub
09-29-2015, 09:24 AM
Thank you, perfect now, much appreciated

mancubus
09-29-2015, 01:15 PM
you are welcome.

please mark the thread as "solved" from Thread Tools dropdown (which is above the top right corner of the first messsage) for future references...