Search function

L

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
 
J

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
 
J

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

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?
 
J

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

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top