Pls help me to solve this problem...

[Guest]

my problem is that i want to copy(link) single character into multicell from
a string.

from single cell --> 3678498 (in A1)
to these multicell --> | 3 | 6 | 7 | 8 | 4 | 9 | 8 |(A2, B2, C2, D2, E2, F2
& G2)
When i tried to use "=MID(A1,1,1)" for (3) in 1st cell (A2), "=MID(A1,2,1)"
for (6) in 2nd cell (B2) etc..., it's ok for 7 digits.
But it's a problem, when i tried to input 6 digits.
The formular lookup from left to right of the string.
So.. any other way to use the formular starting from right to left?
Or ???
Thanks
Tom

 

[Bob Phillips]

This works for me copied across because MID returns blank when beyond the
string

=MID($A1,COLUMN(A1),1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Thanks alot for your formula and it's really interesting.
I like the way MID returns blank when beyond the string.

according to your formula, it'll be like this;

|3|6|7|8|4|9|8|
|4|5|9|4|6|2|-|
|6|3|8|1|9|-|-|

But what i trying to show is;
|3|6|7|8|4|9|8|
|-|4|5|9|4|6|2|
|-|-|6|3|8|1|9|

I want those amount to be in lineup from right to left one, hundred,
thousand,..etc..
Do u have any idea for that?
Thanks again.
Tom

 

[Guest]

Try these
in A2
=IF(ISERR(MID(A1,LEN(A1)-6,1)),"",MID(A1,LEN(A1)-6,1))

in B2
=IF(ISERR(MID(A1,LEN(A1)-5,1)),"",MID(A1,LEN(A1)-5,1))

in C2
=IF(ISERR(MID(A1,LEN(A1)-4,1)),"",MID(A1,LEN(A1)-4,1))

in D2
=IF(ISERR(MID(A1,LEN(A1)-3,1)),"",MID(A1,LEN(A1)-3,1))

in E2
=IF(ISERR(MID(A1,LEN(A1)-2,1)),"",MID(A1,LEN(A1)-2,1))

in F2
=IF(ISERR(MID(A1,LEN(A1)-1,1)),"",MID(A1,LEN(A1)-1,1))

and in G2
=IF(ISERR(RIGHT(A1,1)),"",RIGHT(A1,1))

Hope that helps.

 

[Bob Phillips]

How about this in B1

=IF(LEN($A1)<8-COLUMN(B1)+3,"",--MID($A1,COUNT($A1:A1),1))

and copy across.

It assumes a max of 8 digits. Change the <8 to adapt


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

In B1: =IF(LEN($A1)<COLUMNS(B:$H),"",MID($A1,LEN($A1)+1-COLUMNS(B:$H),1)+0)

copy across and down

 

[Alan Beban]

Or in A2 filled across

=IF(ISERR((MID($A$1,LEN($A$1)-(7-COLUMN(A1)),1))*1),"",(MID($A$1,LEN($A$1)-(7-COLUMN(A1)),1))*1)

Alan Beban

 

[Guest]

I was trying to stay away from a dependency on column/data position.
Unbelievable how often someone asks for something in columns A:G (or A:B) and
we give it to them and then we find out the reality is that the data is over
in Z:AE <g>

 

[Alan Beban]

The only dependency is on the cell in which the series of numbers
resides. How can you have a formula that is not dependent on where that
series of numbers is?

Alan Beban

 

[Harlan Grove]

But what i trying to show is;
|3|6|7|8|4|9|8|
|-|4|5|9|4|6|2|
|-|-|6|3|8|1|9|

....

If these could be text,

B1:
=MID(TEXT($A1,"???????"),COLUMNS($B1:B1),1)

If these need to be numbers, change Bob Phillips's formula to

B1:
=IF(LEN($A1)>=COLUMNS(B1:$H1),--MID($A1,COUNT($A1:A1),1),"")

If the cell containing the original number weren't necessarily in the
column immediately to the left of the result columns, try

B1:
=IF(LEN($A1)>=COLUMNS(B1:$H1),--MID($A1,LEN($A1)+1-COLUMNS(B1:$H1),
1),"")

In either case, fill B1 right into C1:H1.

 

[Harlan Grove]

Try these

in A2
=IF(ISERR(MID(A1,LEN(A1)-6,1)),"",MID(A1,LEN(A1)-6,1))

....

If you're going to do this much hardcoding, might as well simplify.

=IF(LEN(A1)>6,MID(A1,LEN(A1)-6,1),"")

and similarly for B2:G2.

MID would only return an error in the formula above when either A1
evaluated to an error or its 2nd argument wasn't a positive number.
Best to let errors in A1 propagate and only test the length of A1.

 

[Guest]

Wasn't trying to start anything - but other formulas seen here have sometimes
depended on the 'split' being in columns A:G - but if the splits were in
other columns then the use of the column number to determine which character
to pull out of it would not work without modification. I have no argument
that you've got to get the initial value from where ever it is - and in this
case it is set up in A1.

 

[Guest]

Hi! JLatham,

Thanks alot for your formula and it's perfect.
That is what i wanted to do and couldn't find anywhere.
The problem is solved.
Thanks again and have a beautiful day

Tom

 

[Guest]

Thanks, Harlan Grove.
Your formula is simple and short.
But when i copy your formula and paste there, it's an error that showing ("").
So i added some brackets and it's ok..
=IF(LEN(A1)>6,MID(A1,LEN(A1)-6,1),"") -->
=IF(LEN(A1)>6,(MID(A1,LEN(A1)-6,1)),"")

Tom