taking a word out

[childofthe1980s]

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

 

[Jacob Skaria]

Try
=LEFT(A1,FIND(CHAR(32),A1)-1)

If this post helps click Yes

 

[Matthew Herbert]

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

 

[Jacob Skaria]

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

 

[Ron Rosenfeld]

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

 

[Matthew Herbert]

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

 

[JLGWhiz]

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

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

=substitute(a1,"Average","")

should be =substitute(a1," Average","")
--ron