Tough teaser!

[LiAD]

Afternoon,

Is it possible some-one could help with a formula to look inside a text
string mixed of letters, numbers and symbols to return a number that
corresponds to a certain character.

Example of character strings to investigate -

Condition A - 42 N 8 FILS 0,50 or 55 N 12 F 0,50 (two ways of writing this
input)
I need a way of outputting the value before and the value after the N into
two separate cells, so outputs would be 42 and 8 or 55 and 12.

Condition B - 27
I just need it to return the same value, (27)

Condition E - 1 T.2.65CU801+ 9 F.1.05
I need this to return the value before the F, (9).

Condition C - 1 T 120 CU 552 + 68 N 12 F 0,50
As per condition 1, return only the value before and after the N in two
separate cells. The first part before the + is to be ignored. Result 68 and
12

Condition D - 37 T 1,91 ET 851
I need this to return a zero (0) as there is nothing i need to know from this.

Condition E - 3 N 8 F 0,30 + 3 F 0,30
I need this to return three values; the two either side of the N, (3 and 8)
and the one before the F, (3).

Condition F - 3 N 7 F 0,40 + 1 N 6 F 0,40
Result required - the value either side of the two N's - 3 and 7 and 1 and 6

This can be achieved either with formulas or code - depending on what is the
easiest to follow, change if desired and takes the least space. I will have
200 such lines to digest.

Does anyone have any ideas on how best to achieve these results from one
column of 200 input cells going straight to 1-4 columns of results?

Thanks
LiAD

 

[Rick Rothstein]

Two questions for clarification. First, does the "Condition" text appear in
the cell along with the other text (especially for your Condition A)?
Second, for any given condition, are the number of fields in the "code"
portion fixed? For that last question, I mean as an example, for Condition
A, are there always 5 space delimited sections before the comma and a single
number after the comma. If the sections are in fact fixed for each
condition, is there supposed to be a space in front of the plus sign in the
first Condition E like there is in the second Condition E? If so, please
check your other posted conditions for typos and tell us if there are any...
the "shape" of each condition is important in being able to parse the text
the way you want and any typos will produce incorrect solutions.

 

[Joel]

I'm not sure if you comma is a real comma or a Thousand place seperator in a
number. this code may work with some modifications. My idea is to pass the
function two parameters. The first one is the string and the sencond
parameter is a count of which number string you want returned. So the
function will return for the following

37 T 1,91 ET 851

37 for the first number string
1,91 for the 2nd number string
851 for the third number string


Function GetNumber(Target As String, NumCount As Integer) As Variant
Dim Chr As String
Dim Results As String

Count = 0
CharPos = 1
Alpha = True 'used to determin if we are processing letters
Do While Count < NumCount And CharPos <= Len(Target)
Chr = Mid(Target, CharPos, 1)
If IsNumeric(Chr) Or Chr = "'" Or Chr = "." Then
'start new number string
If Alpha = True And IsNumeric(Chr) Then
Alpha = False
Results = Chr
Else
Results = Results & Chr
End If
Else
If Alpha = False Then
Count = Count + 1
End If
Alpha = True
End If
CharPos = CharPos + 1
Loop

If Alpha = False Then
Count = Count + 1
End If
If Count = NumCount Then
GetNumber = Val(Results)
Else
Set GetNumber = Nothing
End If
End Function

 

[LiAD]

Hi,

Thanks for your help.

Condition A etc does not appear in the text string so A would start at the 4
and give 4 spaces.

In short yes it can be variable, depending on a number of 'external'
factors. If it really an important factor, or one that can hugely
simplify/shorten the solution then it can be standardised. For example the
F.1.05 can be replaced by F 1.05. Will a difference between , and . also
create a problem? Again if so it can be standardised.

Thanks for your help

 

[LiAD]

Hi,

Small question - where had you planned on the data and results being for
this to run?

Thanks

 

[Rick Rothstein]

You raised a good question in your response to Joel... where are these text
strings at and where do you want the results placed? Also, how do you
identify which Condition applies to the text string.

 

[LiAD]

Hi,

I guess lets keep it simple, assume the text string is in cells A1-A201 we
would like the results in the adjacent cells B,C,D and E in the same sheet.
Once the code works then I can juggle the destinations and source locations
(as long as I can find where to change them).

I had in mind a formula/code that would look at the text string and based on
what it see's decide where it needs to read the results from. So for example
the 56 N 8 F 0.5 it searches the string, sees the N (with its spaces either
side) and from that knows that it needs to read the value immediately before
and immedictaly after the N. If its sees two N's and a + it knows it needs
to read four values.

I'm not sure how to approach it. In my mind I had some idea of having a
formula that finds the position of the N, if it finds none it looks for a
number, if it does then using that position it finds values it needs. If in
order to do this I need to create additional cells/columns to return a value
that counts how many N's are there, then another one to find the position of
the N's etc, then uses these two values to generate the outputs we can add
columns no problem.

I'm sure this can also be done with formulas but they would be horrible big
things.

In order to simplify further I suggest if we base the formula just on the
values before and after the N, forget about all the others for now, maybe
that will get it started easier. So an example of the different inputs the
code may see and the outputs required just based on the using the values
around the N. For reference there will not be any other N's in the text
string other than the conditions u see below, for example u WILL NOT SEE 1 TN
3.09CU501 or 1 N 8 FNH 0.6, it will always be N with a space either side as
the reference.

Example of all possible input types and the output required based in using
N's.

A B
C D E
42 N 8 FILS 0,50 42 8
27 0
1 T.2.65CU801+ 9 F.1.05 0
1 T 120 CU 552 + 68 N 12 F 0,50 68 12
37 T 1,91 ET 851 0
3 N 8 F 0,30 + 3 F 0,30 3 8
3 N 7 F 0,40 + 1 N 6 F 0,40 3 7
1 6

 

[Rick Rothstein]

I think trying to keep it simple in the way you suggest will just end up
making more work for us overall... let's try to avoid that. The key to
helping you is for you to tell us all of the rules that guide you in
recognizing which condition applies and when. Here is the problem I see in
trying to decipher what to do and when.

Condition A - 55 N 12 F 0,50
Condition E - 3 N 8 F 0,30 + 3 F 0,30

For Condition A, you want the number on either side of the N where as in
Condition E you want the numbers on either side of the N and you also want
the number before the F. But Condition A has an F in it as well... what is
the difference between Condition A's "F" and Condition E's "F" that you want
the number before the second one but not before the first one? Also, for

Condition F - 3 N 7 F 0,40 + 1 N 6 F 0,40

you want the numbers on either side of the two N's only, neither F is being
considered. Do you see the dilemma? There is some set of rules which you
know that helps you recognize which "shape" makes the text fit into a
category... you *must* tell us, in detail, these not yet specified rules if
you expect us to help you arrive at a solution. I am pretty sure we can
create the coded solution you are looking for as long as you tell us the
rules you use in recognizing what is what.

 

[LiAD]

Ah yes, sorry now I see where you are coming from. Apologies for not
understanding.

The difference in the example you give is in comparing

number(x) N number(y) F number(z)
+ number(a) F number(z) (in some cases the + may be omitted)

The + may be omitted and it may read - number(a) F number(z). The +
signifies that this element is mixed with another element.

These conditions are set. Anything that is as per the first line I always
need the first two numbers and anything as per the second line just the first
number.

Just for your info its for an electrical application. Not sure this will
help, just to give some relevance to what I'm sure seems very random. So in
order to install your cooker you need 3 single wires of diameter 2.5mm - your
code will read 3 F 2.5.
However the guy next door he needs his wires to be flexible because his
wires need to over cupboards and into holes, behind stuff etc, generally get
bent all over the place. We will supply him with a cable composed of wires
of 0.2mm made into bunches of 4 wires and he will need 3 of these bunches -
his code will be 3 N 4 F 0.2. If we need something special to be done we'll
give someone else 3 N 4 F 0.2 + 1 F 2.5.

Thanks a million for your help and patience!
LiAD

 

[Rick Rothstein]

I just figured out the rule you are following... you had thrown me with your
"get number from both sides of the N, get number before (certain) F's"
description... you rule, get any number before an N or an F. Here is the
code to do that (let me know if it works correctly for you)...

Sub ParseElectricalCodes()
Dim X As Long, Z As Long, LastRow As Long, C As Range
Dim Code As String, Parts() As String, SubParts() As String
Const DataCol As String = "A"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
For Each C In Range(DataCol & "1:" & DataCol & LastRow)
Code = Replace(Replace(C.Value, " N", Chr(1)), " F", Chr(1))
Parts = Split(Code, Chr(1))
If UBound(Parts) = 0 Then
If IsNumeric(Parts(0)) Then
C.Offset(, 1).Value = Parts(0)
Else
C.Offset(, 1).Value = 0
End If
Else
For X = 0 To UBound(Parts) - 1
If UBound(Parts) > -1 Then
If UBound(Parts) = 0 Then
C.Offset(, 1).Value = Val(Parts(X))
Else
SubParts = Split(Parts(X))
C.Offset(, X + 1).Value = SubParts(UBound(SubParts))
End If
End If
Next
End If
Next
End Sub

 

[LiAD]

Yeah,

Does the job I'm after no probs.

Thanks a million for all your help (and patience)