#1   Report Post  
Old November 21st 05, 09:35 PM posted to microsoft.public.excel.worksheet.functions
Matt M HMS
 
Posts: n/a
Default Trim Function

Hello and thanks in advance -

I have a column of room numbers with [alphas] mixed in as seen below.

A
100
101D
103F
104
1004E

Note that the numeric values are sometimes in the thousand range. I need to
create a column B where the numeric value of the room number will be split up
from column C, the alpha, as below;

A B C
100 100
101D 101 D
103F 103 F
104 104
1004E 1004 E

Matt

  #2   Report Post  
Old November 21st 05, 10:20 PM posted to microsoft.public.excel.worksheet.functions
Lotus123
 
Posts: n/a
Default Trim Function


Assuming the Alpha is always one character and always either absent or
located on the right...here you go:

If you value is in Column A, place this in ColumnB (strip the digits):
+VALUE(IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)))

Place this in Column C (snag the character):
+IF(ISNUMBER(A1),"",RIGHT(A1,1))

This can be modified even more for situations in which the letter
appears at the beginning or if there are two letters; however, I didn't
want to make the forumla unduely complicated on the first run.


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=487007

  #3   Report Post  
Old November 21st 05, 10:35 PM posted to microsoft.public.excel.worksheet.functions
Dave Breitenbach
 
Posts: n/a
Default Trim Function

If there is always only one letter as the alpha, then the following formulas
will work:
[b1]=IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1))
[c1]=IF(ISNUMBER(A1),"",RIGHT(A1,1))

However, if any of your values have spaces at the end, then the data will
have to be trimmed first - simply trim(a1) and then adjust the other formulas
to link to the trimmed version of the data column.

If the alpha can be more than one character then it can probably still be
done but more info will be needed on the rules that the alphas follow...i.e
when is there more than one character, etc.

hth,
Dave

"Matt M HMS" wrote:

Hello and thanks in advance -

I have a column of room numbers with [alphas] mixed in as seen below.

A
100
101D
103F
104
1004E

Note that the numeric values are sometimes in the thousand range. I need to
create a column B where the numeric value of the room number will be split up
from column C, the alpha, as below;

A B C
100 100
101D 101 D
103F 103 F
104 104
1004E 1004 E

Matt

  #4   Report Post  
Old November 21st 05, 10:37 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Trim Function

In B1, use

=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),ROW(IND
IRECT("A1:A"&LEN(A1))),255))-1)

which is an array formula, so commit with Ctrl-Shift-Enter

In C1,

=SUBSTITUTE(A1,B1,"")

and copy doen

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt M HMS" <Matt M wrote in message
...
Hello and thanks in advance -

I have a column of room numbers with [alphas] mixed in as seen below.

A
100
101D
103F
104
1004E

Note that the numeric values are sometimes in the thousand range. I need

to
create a column B where the numeric value of the room number will be split

up
from column C, the alpha, as below;

A B C
100 100
101D 101 D
103F 103 F
104 104
1004E 1004 E

Matt



  #5   Report Post  
Old November 21st 05, 10:42 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Trim Function

=SUBSTITUTE(A2,C,"")

assuming you have the valuse in C, if you want to get the numbers without
using column C

=IF(ISERR(--(A1)),LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LE
N(A1))),1)),0)-1),A1)


entered with ctrl + shift & enter

assuming all numbers are to the left of the letter


--

Regards,

Peo Sjoblom

"Matt M HMS" <Matt M wrote in message
...
Hello and thanks in advance -

I have a column of room numbers with [alphas] mixed in as seen below.

A
100
101D
103F
104
1004E

Note that the numeric values are sometimes in the thousand range. I need

to
create a column B where the numeric value of the room number will be split

up
from column C, the alpha, as below;

A B C
100 100
101D 101 D
103F 103 F
104 104
1004E 1004 E

Matt





  #6   Report Post  
Old November 21st 05, 10:46 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Trim Function

BTW, mine is agnostic to how many letters or numbers are in the string, even
0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
In B1, use


=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),ROW(IND
IRECT("A1:A"&LEN(A1))),255))-1)

which is an array formula, so commit with Ctrl-Shift-Enter

In C1,

=SUBSTITUTE(A1,B1,"")

and copy doen

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matt M HMS" <Matt M wrote in message
...
Hello and thanks in advance -

I have a column of room numbers with [alphas] mixed in as seen below.

A
100
101D
103F
104
1004E

Note that the numeric values are sometimes in the thousand range. I need

to
create a column B where the numeric value of the room number will be

split
up
from column C, the alpha, as below;

A B C
100 100
101D 101 D
103F 103 F
104 104
1004E 1004 E

Matt







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
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 03:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 02:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 10:49 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 07:07 AM


All times are GMT +1. The time now is 01:23 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017