Pls help me to solve this problem...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bradley - 20 Jul 2007 10:54 GMT

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

Reply to this Message

Bob Phillips - 20 Jul 2007 11:02 GMT

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

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

Signature

HTH

Bob

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

> my problem is that i want to copy(link) single character into multicell
> from
[quoted text clipped - 13 lines]
> Thanks
> Tom

Reply to this Message

Bradley - 20 Jul 2007 12:02 GMT

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

> This works for me copied across because MID returns blank when beyond the
> string
[quoted text clipped - 18 lines]
> > Thanks
> > Tom

Reply to this Message

JLatham - 20 Jul 2007 13:16 GMT

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.

> Hi Bob,
>
[quoted text clipped - 40 lines]
> > > Thanks
> > > Tom

Reply to this Message

Alan Beban - 20 Jul 2007 19:22 GMT

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

> Try these
> in A2
[quoted text clipped - 64 lines]
>>>> Thanks
>>>> Tom

Reply to this Message

JLatham - 20 Jul 2007 19:44 GMT

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>

> Or in A2 filled across
>
[quoted text clipped - 69 lines]
> >>>> Thanks
> >>>> Tom

Reply to this Message

Alan Beban - 20 Jul 2007 19:56 GMT

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

> 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
[quoted text clipped - 74 lines]
>>>>>> Thanks
>>>>>> Tom

Reply to this Message

JLatham - 20 Jul 2007 22:40 GMT

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.

> 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
[quoted text clipped - 80 lines]
> >>>>>> Thanks
> >>>>>> Tom

Reply to this Message

Harlan Grove - 20 Jul 2007 20:57 GMT

JLatham <HelpFrom @ Jlathamsite.com.(removethis)> wrote...

>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.

Reply to this Message

Bradley - 21 Jul 2007 02:40 GMT

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

> JLatham <HelpFrom @ Jlathamsite.com.(removethis)> wrote...
> >Try these
[quoted text clipped - 12 lines]
> 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.

Reply to this Message

Bradley - 21 Jul 2007 01:58 GMT

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

> Try these
> in A2
[quoted text clipped - 64 lines]
> > > > Thanks
> > > > Tom

Reply to this Message

Bob Phillips - 20 Jul 2007 14:34 GMT

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

Signature

---
HTH

Bob

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

> Hi Bob,
>
[quoted text clipped - 41 lines]
>> > Thanks
>> > Tom

Reply to this Message

Harlan Grove - 20 Jul 2007 20:42 GMT

Bradley <Brad...@discussions.microsoft.com> wrote...
...

>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.

Reply to this Message

Teethless mama - 20 Jul 2007 15:36 GMT

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

copy across and down

> my problem is that i want to copy(link) single character into multicell from
> a string.
[quoted text clipped - 10 lines]
> Thanks
> Tom

Reply to this Message