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

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

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 -

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 -

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 -

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 -

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 -