|
Search function
Good morning,
I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are --- 0 0 563 ET 761 2 7 5N F0,035 Row 11 inputs are -- 4N F6 2 10 25 CU 3 4 ET 7 12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 5N F0,0035 Row 13 - 4N F6 25 CU 3 4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks |
Search function
provided:
1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are *--- 0 * * * * *0 * * * *563 ET 761 * * * * 2 * * * * * * 7 * * * * 5N F0,035 Row 11 inputs are *-- *4N F6 * *2 * * * * * * *10 * * * * * 25 CU 3 * * 4 ET 7 * * * * *12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 * * * * 5N F0,0035 Row 13 - * * 4N F6 * * * * * * * *25 CU 3 * * * *4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks |
Search function
sorry, missed one bracket
=IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1)),"",OFFSET($A$12,ROW()-13,SMALL(IF (ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1)),"",OFFSET($A$13,ROW()-14,SMALL(IF (ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) On 26 Mar, 11:38, Jarek Kujawa wrote: provided: 1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are *--- 0 * * * * *0 * * * *563 ET 761 * * * * 2 * * * * * * 7 * * * * 5N F0,035 Row 11 inputs are *-- *4N F6 * *2 * * * * * * *10 * * * * * 25 CU 3 * * 4 ET 7 * * * * *12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 * * * * 5N F0,0035 Row 13 - * * 4N F6 * * * * * * * *25 CU 3 * * * *4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
Search function
Sorry I actually just said 12 and 13 for example purposes.
I have actually 100 rows to fill. The output will be start in row AA3, (going to AA103). The input table starts AG237 and will continue to col CV337. Does this make it too long and complicated? "Jarek Kujawa" wrote: sorry, missed one bracket =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1)),"",OFFSET($A$12,ROW()-13,SMALL(IF (ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1)),"",OFFSET($A$13,ROW()-14,SMALL(IF (ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) On 26 Mar, 11:38, Jarek Kujawa wrote: provided: 1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are --- 0 0 563 ET 761 2 7 5N F0,035 Row 11 inputs are -- 4N F6 2 10 25 CU 3 4 ET 7 12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 5N F0,0035 Row 13 - 4N F6 25 CU 3 4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Search function
nope
not much just wait an hour pls On 26 Mar, 12:13, LiAD wrote: Sorry I actually just said 12 and 13 for example purposes. Â* I have actually 100 rows to fill. Â* The output will be start in row AA3, (going to AA103). Â* The input table starts AG237 and will continue to col CV337. Does this make it too long and complicated? "Jarek Kujawa" wrote: sorry, missed one bracket =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1)),"",OFFSET($A$12,ROW()-13,SMALL(IF (ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1)),"",OFFSET($A$13,ROW()-14,SMALL(IF (ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) On 26 Mar, 11:38, Jarek Kujawa wrote: provided: 1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are Â*--- 0 Â* Â* Â* Â* Â*0 Â* Â* Â* Â*563 ET 761 Â* Â* Â* Â* 2 Â* Â* Â* Â* Â* Â* 7 Â* Â* Â* Â* 5N F0,035 Row 11 inputs are Â*-- Â*4N F6 Â* Â*2 Â* Â* Â* Â* Â* Â* Â*10 Â* Â* Â* Â* Â* 25 CU 3 Â* Â* 4 ET 7 Â* Â* Â* Â* Â*12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 Â* Â* Â* Â* 5N F0,0035 Row 13 - Â* Â* 4N F6 Â* Â* Â* Â* Â* Â* Â* Â*25 CU 3 Â* Â* Â* Â*4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Search function
try:
=OFFSET(INDIRECT("$AA$"&ROW()),234,MIN.K(IF(ISTEXT ($AG237:$CV237),COLUMN($AG237:$CV237),""),COLUMN()-26)-27) then copy/drag down and to the right this will leave with many #NUMBER! errors you might copy the whole data and past it special as values, then Edit-Replace What: #NUMBER! With: nothing/leave it blank still working on it On 26 Mar, 12:35, Jarek Kujawa wrote: nope not much just wait an hour pls On 26 Mar, 12:13, LiAD wrote: Sorry I actually just said 12 and 13 for example purposes. Â* I have actually 100 rows to fill. Â* The output will be start in row AA3, (going to AA103). Â* The input table starts AG237 and will continue to col CV337. Does this make it too long and complicated? "Jarek Kujawa" wrote: sorry, missed one bracket =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1)),"",OFFSET($A$12,ROW()-13,SMALL(IF (ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1)),"",OFFSET($A$13,ROW()-14,SMALL(IF (ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) On 26 Mar, 11:38, Jarek Kujawa wrote: provided: 1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces). Row 10 inputs are Â*--- 0 Â* Â* Â* Â* Â*0 Â* Â* Â* Â*563 ET 761 Â* Â* Â* Â* 2 Â* Â* Â* Â* Â* Â* 7 Â* Â* Â* Â* 5N F0,035 Row 11 inputs are Â*-- Â*4N F6 Â* Â*2 Â* Â* Â* Â* Â* Â* Â*10 Â* Â* Â* Â* Â* 25 CU 3 Â* Â* 4 ET 7 Â* Â* Â* Â* Â*12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 Â* Â* Â* Â* 5N F0,0035 Row 13 - Â* Â* 4N F6 Â* Â* Â* Â* Â* Â* Â* Â*25 CU 3 Â* Â* Â* Â*4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Search function
couldn't have come with anything better
but why don't you try to insert the formulae I have provided into some other unused range, say beyond CV column (e.g. DA3) and in AA3 insert sth. like: =IF(ISERROR(DA3,"",DA3) then copy as needed On 26 Mar, 14:03, Jarek Kujawa wrote: try: =OFFSET(INDIRECT("$AA$"&ROW()),234,MIN.K(IF(ISTEXT ($AG237:$CV237),COLUMN($AG237:$CV237),""),COLUMN()-26)-27) then copy/drag down and to the right this will leave with many #NUMBER! errors you might copy the whole data and past it special as values, then Edit-Replace What: #NUMBER! With: nothing/leave it blank still working on it On 26 Mar, 12:35, Jarek Kujawa wrote: nope not much just wait an hour pls On 26 Mar, 12:13, LiAD wrote: Sorry I actually just said 12 and 13 for example purposes. Â* I have actually 100 rows to fill. Â* The output will be start in row AA3, (going to AA103). Â* The input table starts AG237 and will continue to col CV337. Does this make it too long and complicated? "Jarek Kujawa" wrote: sorry, missed one bracket =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1)),"",OFFSET($A$12,ROW()-13,SMALL(IF (ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1)),"",OFFSET($A$13,ROW()-14,SMALL(IF (ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) On 26 Mar, 11:38, Jarek Kujawa wrote: provided: 1. you have cells with numeric data inserted as Numbers 2. you only need rows 12 and 13 to be populated would the following be what you're expecting: in A12 and A13 respectively: =IF(ISERROR(OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT($A$10:$F$10),COLUMN ($A$10:$F$10),""),COLUMN())-1),"",OFFSET($A$12,ROW()-13,SMALL(IF(ISTEXT ($A$10:$F$10),COLUMN($A$10:$F$10),""),COLUMN())-1)) =IF(ISERROR(OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT($A$11:$F$11),COLUMN ($A$11:$F$11),""),COLUMN())-1),"",OFFSET($A$13,ROW()-14,SMALL(IF(ISTEXT ($A$11:$F$11),COLUMN($A$11:$F$11),""),COLUMN())-1)) then drag/copy right CTRL+SHIFT+ENTER this as it is an array-formula pls click YES if this post helped you On 26 Mar, 10:56, LiAD wrote: Good morning, I have a list of dt 7a (mixture of text and numbers) arranged in a horizontal column such as below, (for ref 563 ET 761 is the contents of one cell, all fields below are single cells, just some have spaces).. Row 10 inputs are Â*--- 0 Â* Â* Â* Â* Â*0 Â* Â* Â* Â*563 ET 761 Â* Â* Â* Â* 2 Â* Â* Â* Â* Â* Â* 7 Â* Â* Â* Â* 5N F0,035 Row 11 inputs are Â*-- Â*4N F6 Â* Â*2 Â* Â* Â* Â* Â* Â* Â*10 Â* Â* Â* Â* Â* 25 CU 3 Â* Â* 4 ET 7 Â* Â* Â* Â* Â*12 The contents of the non numeric cells are completely changeable between different characters, numbers, spaces and position in the horizontal row. I would like to output, in adjacent cells (cols A, B, C for example) in row 12 and 13 just the non numeric data. Row 12 - 563 ET 761 Â* Â* Â* Â* 5N F0,0035 Row 13 - Â* Â* 4N F6 Â* Â* Â* Â* Â* Â* Â* Â*25 CU 3 Â* Â* Â* Â*4 ET 7 There are no only numerical cells that i need to output, just the cells that contain mixed text and numbers. Does anyone know the simplest way to create this output? Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| All times are GMT +1. The time now is 01:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com