excel text placement

  bahati0ne 15:51 22 Feb 06
Locked

hi,

i am trying to get excel to copy some text from one cell to another if another cell has either a single letter or number in it.

example.

if cell A1 has 1 (or A) in it i want cell d1 to equal cell f1

is this possible please?

  stlucia 16:42 22 Feb 06

You use the LEN function if it's text in cell A1, but if it's a number you'll simply test for whether it's value is less than 10.

You'll then combine them using the OR function, so the formula in cell d1 will look something like OR(LEN(a1)=0,a1<10,f1)

  bahati0ne 18:47 22 Feb 06

thanks
but all i now get in cell di is true or false. how do i get it to repeat the actual text in cell f1?

  VoG II 19:02 22 Feb 06

In D1

=IF(A1<10,F1,IF(LEN(A1)=1,F1,""))

  JWJ 19:29 22 Feb 06

It is hard to know for sure what you need without seeing the application. VoG's solution is good if you can't ever have a negative number in A1.

As an alternative you could try this in D1.
+IF(LEN(TEXT(A1,"0"))=1,F1,"")

This works for all situations except for A1 being blank.

  JWJ 19:31 22 Feb 06

Further to that, this works for all situations (I think)

=+IF(A4<>"",IF(LEN(TEXT(A4,"0"))=1,F4,""),"")

  JWJ 19:32 22 Feb 06

Darn it ... sorry

=+IF(A1<>"",IF(LEN(TEXT(A1,"0"))=1,F1,""),"")

  JWJ 19:32 22 Feb 06

Darn it ... sorry

=+IF(A1<>"",IF(LEN(TEXT(A1,"0"))=1,F1,""),"")

  bahati0ne 22:25 22 Feb 06

thanks all

this last one works for all figures in cell A1,

I want it to work for only 1.

i intend to use same formula modified for A2 etc.
with A2 = 2 and puting text from F2 into D2

and so on up to A20

  VoG II 22:32 22 Feb 06

Copy the formula down.

  VoG II 22:38 22 Feb 06

But you will need to modify the formula to use absolute references:

=+IF($A$<>"",IF(LEN(TEXT($A$,"0"))=1,$F1,""),"")

This thread is now locked and can not be replied to.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…