Search function

[LiAD]

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

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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

 

[LiAD]

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]

nope
not much

just wait an hour pls

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?





- Pokaż cytowany tekst -

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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