excel text placement

  bahati0ne 15:51 22 Feb 2006
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 2006

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 2006

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 2006

In D1

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

  JWJ 19:29 22 Feb 2006

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 2006

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

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

  JWJ 19:32 22 Feb 2006

Darn it ... sorry

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

  JWJ 19:32 22 Feb 2006

Darn it ... sorry

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

  bahati0ne 22:25 22 Feb 2006

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 2006

Copy the formula down.

  VoG II 22:38 22 Feb 2006

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.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone