Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Pls help me to solve this problem...

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Pls help me to solve this problem...

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)

"Bradley" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Pls help me to solve this problem...

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Pls help me to solve this problem...

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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Pls help me to solve this problem...

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)



"Bradley" wrote in message
...
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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Pls help me to solve this problem...

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

copy across and down


"Bradley" wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 200
Default Pls help me to solve this problem...

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
JLatham wrote:
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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Pls help me to solve this problem...

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" wrote:

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
JLatham wrote:
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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 200
Default Pls help me to solve this problem...

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

JLatham wrote:
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" wrote:

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
JLatham wrote:
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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Pls help me to solve this problem...

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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Pls help me to solve this problem...

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.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Pls help me to solve this problem...

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.

"Alan Beban" wrote:

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

JLatham wrote:
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" wrote:

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
JLatham wrote:
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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Pls help me to solve this problem...

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

"JLatham" wrote:

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.

"Bradley" wrote:

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

"Bob Phillips" wrote:

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)

"Bradley" wrote in message
...
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



  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Pls help me to solve this problem...

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

"Harlan Grove" wrote:

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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please solve this problem. ramulu Excel Worksheet Functions 1 February 15th 07 07:43 AM
Too many formats - how solve this problem, Manoj Mathew Excel Worksheet Functions 1 December 10th 06 08:22 AM
please solve the problem somaraju Excel Discussion (Misc queries) 1 February 23rd 06 11:17 AM
How to solve problem (equal to 0) with unknown value nj125 Excel Discussion (Misc queries) 0 May 19th 05 03:31 AM
Can someone solve a problem for me? Jon Parker Excel Discussion (Misc queries) 1 April 25th 05 11:14 PM


All times are GMT +1. The time now is 11:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"