Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 70
Default Keep last 4 characters of value

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4 characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 70
Default Keep last 4 characters of value

=right(a1,4) returns the last 4 digits but how do I get the text preceeding
the last 4 please?

"ArcticWolf" wrote:

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4 characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default Keep last 4 characters of value

=LEFT(A1,LEN(A1)-4)

"ArcticWolf" wrote in message
...
=right(a1,4) returns the last 4 digits but how do I get the text
preceeding
the last 4 please?

"ArcticWolf" wrote:

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4
characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Keep last 4 characters of value

On Thu, 15 Nov 2007 03:26:00 -0800, ArcticWolf
wrote:

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4 characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW


B1: =RIGHT(A1,4)
or =MOD(A1,10000)

C1: =LEFT(A1,LEN(A1)-4)
or =INT(A1/10000)

The TEXT formula will return an error if LEN(A1) is not 4; the Math formula
will return a "0" in that case.

It's not clear what result you want in those instances.

--ron
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 70
Default Keep last 4 characters of value

Thanks for the swift reply Stephen - works perfect.

"Stephen" wrote:

=LEFT(A1,LEN(A1)-4)

"ArcticWolf" wrote in message
...
=right(a1,4) returns the last 4 digits but how do I get the text
preceeding
the last 4 please?

"ArcticWolf" wrote:

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4
characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Keep last 4 characters of value

Not sure if I have understood this right but you could try this:

=LEFT(A1, LEN(A1)-4)

This will remove the last 4 characters from your cell

"ArcticWolf" wrote:

=right(a1,4) returns the last 4 digits but how do I get the text preceeding
the last 4 please?

"ArcticWolf" wrote:

Hi,

I need to remove the last 4 characters of value (b1).
I also need to remove all preceeding characters except the last 4 characters
of a value (c1)
Each value is of a different lenght.

IE

a1= 1234567890 (original value)
b1=123456
c1=7890

Thanks in advance,

AW

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
How many characters in a box..? Misha Excel Worksheet Functions 3 June 12th 06 08:33 PM
In Excel find characters when multiple characters exist w/i a cel teacher-deburg Excel Worksheet Functions 1 December 5th 05 11:22 PM
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT GRYSYF Excel Worksheet Functions 5 October 12th 05 10:58 AM
sum of characters ddwchs Excel Discussion (Misc queries) 3 August 5th 05 04:29 AM
Hex Characters Julie Excel Worksheet Functions 1 February 25th 05 10:40 PM


All times are GMT +1. The time now is 03:50 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"