Real 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

 

[Gary''s Student]

This is only a partial solution (Conditions A, C, & F):

Sub nPicker()
Dim v As String
For Each r In Selection
v = r.Value
s = Split(v, " ")
out1 = ""
out2 = ""
out3 = ""
out4 = ""
secondd = False
For i = 0 To UBound(s)
If s(i) = "N" Then
If secondd = False Then
out1 = s(i - 1)
out2 = s(i + 1)
secondd = True
Else
out3 = s(i - 1)
out4 = s(i + 1)
End If
End If
Next
r.Offset(0, 1) = out1
r.Offset(0, 2) = out2
r.Offset(0, 3) = out3
r.Offset(0, 4) = out4
Next
End Sub

So for input like:
1 N 3
dsfjdsljf 1 N 45 ksdhfkds
dsdsf 1 N 65 jkaywrkgk 99 N 3 dksh


The adjacent columns will contain:
1 3
1 45
1 65 99 3

 

[Glenn]

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

Maybe some of these will help...

Before First "N":

=TRIM(MID(REPT(" ",9)&SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",REPT(" ",9)&SUBSTITUTE(A1," ",REPT(" ",9)))-18,17))



After First "N":

=TRIM(MID(REPT(" ",9)&SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",REPT(" ",9)&SUBSTITUTE(A1," ",REPT(" ",9)))+1,18))



Before Second "N":

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",SUBSTITUTE(A1," ",REPT(" ",9)))+1)-18,17))



After Second "N":

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",SUBSTITUTE(A1," ",REPT(" ",9)),
FIND("N",SUBSTITUTE(A1," ",REPT(" ",9)))+1)+1,18))

 

[p45cal]

Can the rules be condensed into:
1. if there's just a single numerical value, just return that
2. else if it's more than a single number then return every value
_before_ each and all of the characters *N* and/or *F*, (regardless if
that *F* is part of *F*ILS or not) (Will there ever be more than 4?)
3. and if there aren't any, return 0

You'll need to look at your data and see if values to the right of *N
*are all followed by *F*.

If so.. next instalment.

 

[LiAD]

Great thanks a lot!

Good idea to start with cons A,C,F.

Just one question where does it generate the output? If i try to run it I
cannot find the output. I have the input strings in col A.

Thanks

 

[LiAD]

Thanks a lot for this.

Works perfect.

I had posted this as well in the programming section thinking the formulas
way would be too long and take too much space but its not that bad actually.

Thanks a lot for your help
LiAD