# 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

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

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough fights back against sexual misconduct

WWDC history: Apple's product launches since 2005

Espace de stockage : comment libérer de la mémoire sur votre iPhone ?