ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Pls help me to solve this problem... (https://www.excelbanter.com/excel-worksheet-functions/151002-pls-help-me-solve-problem.html)

Bradley
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

Bob Phillips
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



Bradley
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




JLatham
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




Bob Phillips
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

Teethless mama
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

Alan Beban
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



JLatham
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




Alan Beban
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


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

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

JLatham
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



Bradley
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




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


All times are GMT +1. The time now is 09:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com