I have a text string in Column A
XX70909 2 NC Black 6pc Box Set 990364068001 15.00 30.00
using in Column B
=VALUE(MID(RIGHT(A1;15);FIND(" ";RIGHT(A1;15))+1;FIND(".";RIGHT
(A1;15))-1))
returns 15.00 which is right
but the same formula on
XX909 1 NC Black 3pc Box Set 990364068002 3.50 14.00
adjusting to
=VALUE(MID(RIGHT(A2;15);FIND(" ";RIGHT(A2;15))+1;FIND(".";RIGHT
(A2;11))-1))
gets it right
How can I get the right value and also split out "NC Black 3pc Box
Set" into Column C
and "990364068002" into Column D?
It's a little hard to tell exactly what you are wanting to do.
For example, in your second example, it seems you are only extracting "3" and
not "3.50" (at least that's what I got on my machine.
The Data/Text-to-Columns wizard will be tough to use to extract the product
name.
I would suggest a User Defined Function using what are called Regular
Expressions.
I have written an expression which breaks your string into 5 groups,
corresponding to your
Prefix
Product Name
Long number (it tests by looking for the first number that is at least
5 digits.
First "price"
Second "price".
You can specify the Index in the formula to return whichever value you want.
For example, given your specs, and if I understand you correctly:
B1: =--ParseData(A1,4)
C1: =ParseData(A1,2)
D1: =ParseData(a1,3)
The double unary in the B1 formula converts the returned "text" value into a
number.
By varying the index argurment, you can return whichever segment of the string
you wish.
If the formula returns a #VALUE! error, it means your data entry did not match
the pattern that you showed above. But the expression can probably be tweaked.
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
==============================================
Option Explicit
Function ParseData(s As String, Index As Long) As Variant
Dim re As Object, mc As Object
'Pattern: Index 1 = Prefix
' Index 2 = Description
' Index 3 = Long number
' Index 4 = First Price
' Index 5 = Second Price
Const sPat As String = "^(\w+)\s+(.*)\s+(\d{5,})\s+([\d.]+)\s+([\d.]+)"
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
Set mc = re.Execute(s)
ParseData = mc(0).submatches(Index - 1)
End Function
=================================
--ron
