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

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 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

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" wrote:

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

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 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

Hi,

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

Thanks

"Joel" wrote:

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" wrote:

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

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
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

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
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

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
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

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
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

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
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

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