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

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

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

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

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

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

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

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

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

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

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