Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Just a brain teaser (for the math guys) | New Users to Excel | |||
Custom EXCEL chart value axis BRAIN TEASER | Charts and Charting in Excel | |||
Brain Teaser | Excel Discussion (Misc queries) | |||
real number | Excel Discussion (Misc queries) | |||
Format Background Color Teaser | Excel Discussion (Misc queries) |