Yeah,

Does the job I'm after no probs.

Thanks a million for all your help (and patience)

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
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