replacing values in Excel

N

nikos

Hi to everyone.
I have a column in an Excel sheet (lets say A10:A20). The column includes
strings, numbers and the symbols (-) and (").
I would like from this column to extract in B10:B20 the corresponding values
(Bi=Ai) in all cells from A column, if the values are <> - or <> " . Else if
the cell include -, or " then, i would like to replace them, by the nearest
above other string.
Here is a simple example.

The A column:


A


A1
A2
-
-
-
A6
A7
"
"
A10


and the B column which must extract is:


A1
A2
A2
A2
A2
A6
A7
A7
A7
A10

Thank you.
 
P

Pete_UK

Using your example data, you can put this in B1:

=A1

and this in B2:

=IF(OR(A2="-",A2=CHAR(34)),B1,A2)

Copy this down to B10.

Note that A1 must contain a valid string.

Hope this helps.

Pete
 
N

nikos

Short and smart. Thank you.

Pete_UK said:
Using your example data, you can put this in B1:

=A1

and this in B2:

=IF(OR(A2="-",A2=CHAR(34)),B1,A2)

Copy this down to B10.

Note that A1 must contain a valid string.

Hope this helps.

Pete
 

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