software

Hi there!

This is my first post, so please have mercy on my soul.

In one cell I have a character string, e.g. 1011-1

I want to extract the 3rd and 4th character to a new cell, i.e. quot;11quot;
above, as a number.

I was thinking of using a trim-type function to remove the first two
characters and everything after (and including) the hypen. Problem: the
trim function seems to only remove spaces.

I'd like to do this in the little fx window rather than using anything
fancy like VBA.

Thanks for any help you can provide. --
Tommi
------------------------------------------------------------------------
Tommi's Profile: www.excelforum.com/member.php...oamp;userid=30942
View this thread: www.excelforum.com/showthread...hreadid=506143
Try this, with your data in A1

=MID(A1,3,2) 0--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506143have a look in HELP index for MID

--
Don Guillett
SalesAid Software

quot;Tommiquot; gt; wrote in
message ...
gt;
gt; Hi there!
gt;
gt; This is my first post, so please have mercy on my soul.
gt;
gt; In one cell I have a character string, e.g. 1011-1
gt;
gt; I want to extract the 3rd and 4th character to a new cell, i.e. quot;11quot;
gt; above, as a number.
gt;
gt; I was thinking of using a trim-type function to remove the first two
gt; characters and everything after (and including) the hypen. Problem: the
gt; trim function seems to only remove spaces.
gt;
gt; I'd like to do this in the little fx window rather than using anything
gt; fancy like VBA.
gt;
gt; Thanks for any help you can provide.
gt;
gt;
gt; --
gt; Tommi
gt; ------------------------------------------------------------------------
gt; Tommi's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30942
gt; View this thread: www.excelforum.com/showthread...hreadid=506143
gt;
Hi Tommi

With the value containing the string try
=MID(A1,3,2)
The 3 tells the Mid function to start at the 3rd character position
The 2 tells it to take 2 characters.

If you want the resulting 11 to be numeric instead of text, then precede
the formula with the double unary minus -- which will coerce the text
value to a number
=--MID(A1,3,2)

--
Regards

Roger Govierquot;Tommiquot; gt; wrote in
message ...
gt;
gt; Hi there!
gt;
gt; This is my first post, so please have mercy on my soul.
gt;
gt; In one cell I have a character string, e.g. 1011-1
gt;
gt; I want to extract the 3rd and 4th character to a new cell, i.e. quot;11quot;
gt; above, as a number.
gt;
gt; I was thinking of using a trim-type function to remove the first two
gt; characters and everything after (and including) the hypen. Problem:
gt; the
gt; trim function seems to only remove spaces.
gt;
gt; I'd like to do this in the little fx window rather than using anything
gt; fancy like VBA.
gt;
gt; Thanks for any help you can provide.
gt;
gt;
gt; --
gt; Tommi
gt; ------------------------------------------------------------------------
gt; Tommi's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30942
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=506143
gt;
Is your data all the same length?

If so then just copy your column to the next column, select all the data and
do data / text to columns / fixed width, put in breaks after the 2nd and 4th
characters, hit next and then click on first and last columns in the dialog
box and choose 'do not import'

Hit Ok and you are done.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

quot;Tommiquot; gt; wrote in
message ...
gt;
gt; Hi there!
gt;
gt; This is my first post, so please have mercy on my soul.
gt;
gt; In one cell I have a character string, e.g. 1011-1
gt;
gt; I want to extract the 3rd and 4th character to a new cell, i.e. quot;11quot;
gt; above, as a number.
gt;
gt; I was thinking of using a trim-type function to remove the first two
gt; characters and everything after (and including) the hypen. Problem: the
gt; trim function seems to only remove spaces.
gt;
gt; I'd like to do this in the little fx window rather than using anything
gt; fancy like VBA.
gt;
gt; Thanks for any help you can provide.
gt;
gt;
gt; --
gt; Tommi
gt; ------------------------------------------------------------------------
gt; Tommi's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30942
gt; View this thread: www.excelforum.com/showthread...hreadid=506143
gt;
gt; With the value containing the string try
That should have read
With A1 containing the string, try
=MID(A1,3,2)

--
Regards

Roger Govierquot;Roger Govierquot; gt; wrote in message
...
gt; Hi Tommi
gt;
gt; With the value containing the string try
gt; =MID(A1,3,2)
gt; The 3 tells the Mid function to start at the 3rd character position
gt; The 2 tells it to take 2 characters.
gt;
gt; If you want the resulting 11 to be numeric instead of text, then
gt; precede the formula with the double unary minus -- which will coerce
gt; the text value to a number
gt; =--MID(A1,3,2)
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Tommiquot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; Hi there!
gt;gt;
gt;gt; This is my first post, so please have mercy on my soul.
gt;gt;
gt;gt; In one cell I have a character string, e.g. 1011-1
gt;gt;
gt;gt; I want to extract the 3rd and 4th character to a new cell, i.e. quot;11quot;
gt;gt; above, as a number.
gt;gt;
gt;gt; I was thinking of using a trim-type function to remove the first two
gt;gt; characters and everything after (and including) the hypen. Problem:
gt;gt; the
gt;gt; trim function seems to only remove spaces.
gt;gt;
gt;gt; I'd like to do this in the little fx window rather than using
gt;gt; anything
gt;gt; fancy like VBA.
gt;gt;
gt;gt; Thanks for any help you can provide.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Tommi
gt;gt; ------------------------------------------------------------------------
gt;gt; Tommi's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=30942
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=506143
gt;gt;
gt;
gt;

Wow, thanks guys. More than awesome!--
Tommi
------------------------------------------------------------------------
Tommi's Profile: www.excelforum.com/member.php...oamp;userid=30942
View this thread: www.excelforum.com/showthread...hreadid=506143