Splitting a cell by number of characters from the end
 

Is there a way to split a cell by specifiying how many characters from the
end? This sounds crazy but here is what I mean, the cell might contain in A1
for example:

"Here is the product code: 1296.2496.80."

Can I split A1 into 5 different cells like this:

B1 = characters 1,2, & 3 from the end which would yield "80."
C1 = characters 4, 5, & 6 from the end which would yield "96."
D1 = characters 7 & 8 from the end which would yield "24"
E1 = characters 9, 10, & 11 from the end which would yield "96."
F1 = characters 9, 10, & 11 from the end which would yield "12"

I would prefer to split it this way since I am having to retrofit something
into a huge project but if that isn't possible your suggestion on a website
that explains the various ways you can split a cell it would be greatly
appreciated. Thank you.

--
David P.

Splitting a cell by number of characters from the end
 

the mid() function will do what you want the mid function starts on the left
side not the right
in B1 enter
=mid(A1,11,3)
in C1
=mid(A1,8,3)
etc.
if you have any leading spaces, you may have to change the numbering
somewhat.

"David P." wrote:

Is there a way to split a cell by specifiying how many characters from the
end? This sounds crazy but here is what I mean, the cell might contain in A1
for example:

"Here is the product code: 1296.2496.80."

Can I split A1 into 5 different cells like this:

B1 = characters 1,2, & 3 from the end which would yield "80."
C1 = characters 4, 5, & 6 from the end which would yield "96."
D1 = characters 7 & 8 from the end which would yield "24"
E1 = characters 9, 10, & 11 from the end which would yield "96."
F1 = characters 9, 10, & 11 from the end which would yield "12"

I would prefer to split it this way since I am having to retrofit something
into a huge project but if that isn't possible your suggestion on a website
that explains the various ways you can split a cell it would be greatly
appreciated. Thank you.

--
David P.

Splitting a cell by number of characters from the end
 

Thank you bj,

The only problem is that if the count starts from the left it won't work
based on how I have this project set up. I could rework work but it here is
another thought, is there a way that the mid function could start the count
from a particular character like a colon or semicolon to mark the starting
point counting to the right? Just as I have the colon in "Here is the product
code:". The reason I say that is because the number of spaces from the left
will change constantly. Thank you again for your suggestions.


--
David P.


"bj" wrote:

the mid() function will do what you want the mid function starts on the left
side not the right
in B1 enter
=mid(A1,11,3)
in C1
=mid(A1,8,3)
etc.
if you have any leading spaces, you may have to change the numbering
somewhat.

"David P." wrote:

Is there a way to split a cell by specifiying how many characters from the
end? This sounds crazy but here is what I mean, the cell might contain in A1
for example:

"Here is the product code: 1296.2496.80."

Can I split A1 into 5 different cells like this:

B1 = characters 1,2, & 3 from the end which would yield "80."
C1 = characters 4, 5, & 6 from the end which would yield "96."
D1 = characters 7 & 8 from the end which would yield "24"
E1 = characters 9, 10, & 11 from the end which would yield "96."
F1 = characters 9, 10, & 11 from the end which would yield "12"

I would prefer to split it this way since I am having to retrofit something
into a huge project but if that isn't possible your suggestion on a website
that explains the various ways you can split a cell it would be greatly
appreciated. Thank you.

--
David P.

Splitting a cell by number of characters from the end
 

if the product code is always 13 characters add the right() function
and try
in B1
=mid(right(A1,13),11,3)
in C1
=mid(right(A1,13),8,3)

etc
if the number of digits change in the product code
but the relationship to the ":" is always the same
in B1
=mid(A1,find(":",A1)+12,3)
in C1
=mid(A1,find(":",A1)+9,3)
etc
You might have to change the adder depending on the true relationship




"David P." wrote:

Thank you bj,

The only problem is that if the count starts from the left it won't work
based on how I have this project set up. I could rework work but it here is
another thought, is there a way that the mid function could start the count
from a particular character like a colon or semicolon to mark the starting
point counting to the right? Just as I have the colon in "Here is the product
code:". The reason I say that is because the number of spaces from the left
will change constantly. Thank you again for your suggestions.


--
David P.


"bj" wrote:

the mid() function will do what you want the mid function starts on the left
side not the right
in B1 enter
=mid(A1,11,3)
in C1
=mid(A1,8,3)
etc.
if you have any leading spaces, you may have to change the numbering
somewhat.

"David P." wrote:

Is there a way to split a cell by specifiying how many characters from the
end? This sounds crazy but here is what I mean, the cell might contain in A1
for example:

"Here is the product code: 1296.2496.80."

Can I split A1 into 5 different cells like this:

B1 = characters 1,2, & 3 from the end which would yield "80."
C1 = characters 4, 5, & 6 from the end which would yield "96."
D1 = characters 7 & 8 from the end which would yield "24"
E1 = characters 9, 10, & 11 from the end which would yield "96."
F1 = characters 9, 10, & 11 from the end which would yield "12"

I would prefer to split it this way since I am having to retrofit something
into a huge project but if that isn't possible your suggestion on a website
that explains the various ways you can split a cell it would be greatly
appreciated. Thank you.

--
David P.

Splitting a cell by number of characters from the end
 

That's fantastic bj. I will try that out. I'm very optimistic it will work
and I appreciate your help.
--
David P.


"bj" wrote:

if the product code is always 13 characters add the right() function
and try
in B1
=mid(right(A1,13),11,3)
in C1
=mid(right(A1,13),8,3)

etc
if the number of digits change in the product code
but the relationship to the ":" is always the same
in B1
=mid(A1,find(":",A1)+12,3)
in C1
=mid(A1,find(":",A1)+9,3)
etc
You might have to change the adder depending on the true relationship




"David P." wrote:

Thank you bj,

The only problem is that if the count starts from the left it won't work
based on how I have this project set up. I could rework work but it here is
another thought, is there a way that the mid function could start the count
from a particular character like a colon or semicolon to mark the starting
point counting to the right? Just as I have the colon in "Here is the product
code:". The reason I say that is because the number of spaces from the left
will change constantly. Thank you again for your suggestions.


--
David P.


"bj" wrote:

the mid() function will do what you want the mid function starts on the left
side not the right
in B1 enter
=mid(A1,11,3)
in C1
=mid(A1,8,3)
etc.
if you have any leading spaces, you may have to change the numbering
somewhat.

"David P." wrote:

Is there a way to split a cell by specifiying how many characters from the
end? This sounds crazy but here is what I mean, the cell might contain in A1
for example:

"Here is the product code: 1296.2496.80."

Can I split A1 into 5 different cells like this:

B1 = characters 1,2, & 3 from the end which would yield "80."
C1 = characters 4, 5, & 6 from the end which would yield "96."
D1 = characters 7 & 8 from the end which would yield "24"
E1 = characters 9, 10, & 11 from the end which would yield "96."
F1 = characters 9, 10, & 11 from the end which would yield "12"

I would prefer to split it this way since I am having to retrofit something
into a huge project but if that isn't possible your suggestion on a website
that explains the various ways you can split a cell it would be greatly
appreciated. Thank you.

--
David P.