Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
Hello:
I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
Try
=LEFT(A1,FIND(CHAR(32),A1)-1) If this post helps click Yes --------------- Jacob Skaria "childofthe1980s" wrote: Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
On Aug 18, 10:28*am, childofthe1980s
wrote: Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s childofthe1980s, If your cell has one space in it then you can use some of the string functions available in Excel. A1:189604 Average B1: =LEFT(A1,LEN(A1)-FIND(" ",A1)-1) Best, Matthew Herbert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
Select the range and try the below macro
Sub Macro() For Each cell In Selection cell.Value = Trim(Replace(cell.Text, "Average", Chr(32))) Next End Sub If this post helps click Yes --------------- Jacob Skaria "childofthe1980s" wrote: Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
On Tue, 18 Aug 2009 09:28:01 -0700, childofthe1980s
wrote: Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s =substitute(a1,"Average","") --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
On Aug 18, 10:42*am, Matthew Herbert wrote:
On Aug 18, 10:28*am, childofthe1980s wrote: Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s childofthe1980s, If your cell has one space in it then you can use some of the string functions available in Excel. A1:189604 Average B1: =LEFT(A1,LEN(A1)-FIND(" ",A1)-1) Best, Matthew Herbert childofthe1980s, Sorry, I made a typo. The formula should be the following: B1: =LEFT(A1,FIND(" ",A1)-1) Best, Matt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
If data is in cell A1 this formula will return everything before the first space =LEFT(A1,FIND(" ",A1)-1) -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126447 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
select the cells and run this macro:Sub blah() For Each cll In Selection.Cells cll.Value = Replace(cll.Value, " Average", "") Next cll End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126447 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
This will loop down the column and return the item number via a message box
for each entry. Sub getItemNbr() Dim lr As long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A2:A" & lr) 'assumes hdr row For Each c In rng If c < "" Then x = Left(c, InStr(c, " ") - 1) MsgBox x End If Next End Sub "childofthe1980s" wrote in message ... Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
You have posted in a programming newsgroup, so you code code and formula
solutions; however, you can do this directly without either. Select the entire column (or all the cells with data, your choice), click Edit/Replace on Excel's menu bar, type this into the Find What field.... _average where you would use a space character for the underline character I showed and leave the Replace With field blank. If all the options are not showing, click the Option button and make sure the Match Case CheckBox is *not* checked; then click the Replace All button. That should do what you want. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: I have a column that contains an inventory item number followed by a space and then the word "Average". How do I take out the space and "Average", so that I can just have the inventory item number? There is no set number of characters for the inventory item number. childofthe1980s |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
taking a word out
On Tue, 18 Aug 2009 12:44:13 -0400, Ron Rosenfeld
wrote: =substitute(a1,"Average","") should be =substitute(a1," Average","") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Taking a list | Excel Discussion (Misc queries) | |||
Sumif but taking a value | Excel Worksheet Functions | |||
Taking Flat Data in Excel and Making it Relational in Word | Excel Programming | |||
taking two worksheets together | Excel Worksheet Functions | |||
Taking Attendance | Excel Discussion (Misc queries) |