#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 386
Default Real teaser!

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Real teaser!

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

--
Gary''s Student - gsnu200901


"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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Real teaser!

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



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))
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 386
Default Real teaser!

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200901


"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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 386
Default Real teaser!

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

"Glenn" wrote:

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



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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Just a brain teaser (for the math guys) greenusmarine53 New Users to Excel 3 June 19th 09 11:31 PM
Custom EXCEL chart value axis BRAIN TEASER MikeM_work Charts and Charting in Excel 1 September 8th 08 06:49 PM
Brain Teaser John McCabe Excel Discussion (Misc queries) 2 July 31st 08 04:03 PM
real number kontraa Excel Discussion (Misc queries) 4 April 3rd 06 12:48 PM
Format Background Color Teaser nastech Excel Discussion (Misc queries) 0 November 1st 05 09:51 PM


All times are GMT +1. The time now is 05:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"