| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search and return adjacent value
I have a file as follows
3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
how is this data layed out? All in one cell?
FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Yes, it is a comma delimited file which imports into onecell per row in
Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
hi,
would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Yes please, or in column A and B of the existing sheet if that is easier?
"FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
hi,
About half done. need to adjust based on your last post and add loop. hold on. working. how big is the import? FSt1 "JBS" wrote: Yes please, or in column A and B of the existing sheet if that is easier? "FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Try this in B1 and drag down.
=MID(A1,FIND(4103,A1,1)+5,(FIND(",",A1,FIND(4103,A 1,1)+5))-(FIND(4103,A1,1)+5)) Mike "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i referance an adjacent cell from a search? | Excel Discussion (Misc queries) | |||
| Return adjacent cell if conditional formatting exists. | Excel Worksheet Functions | |||
| Return value in adjacent cell | Excel Worksheet Functions | |||
| return value in adjacent cell different workbook | Excel Worksheet Functions | |||
| test a range of cells and return an adjacent value | Excel Worksheet Functions | |||
Hybrid Mode
