Removing everything after a character

J

Javi

Is there a way to remove everything in a field after a certain character
using excel or anything else.

It's from a product datafeed that has this format:

Product name #85423
Product Name2 #84216
Product Name3 #51354
etc..

I want to get rid of everything after #

TIA,
Javi :)
 
H

Harald Staff

Hi Javi

You could try the Excel function Text to Columns (in the Data menu) and
split with # as delimiter.
 
R

Ron Rosenfeld

Is there a way to remove everything in a field after a certain character
using excel or anything else.

It's from a product datafeed that has this format:

Product name #85423
Product Name2 #84216
Product Name3 #51354
etc..

I want to get rid of everything after #

TIA,
Javi :)
Click to expand...

In addition to Harald's method, you could use a formula approach:

=REPLACE(A1,FIND("#",A1)+1,255,"")


--ron
 
D

dalstar

Ron Rosenfeld said:
In addition to Harald's method, you could use a formula approach:

=REPLACE(A1,FIND("#",A1)+1,255,"")
Click to expand...

Can you explain the processing of this formula...I can see it works, but not
sure how (what are the different functions)
 
K

Kevin Stecyk

dalstar,

I thought it was an interesting formula too, so I spent some time to attempt
to figure it out as well. Ron or others, please correct me if I am wrong.

Let's start in the inner portion and work our way outwards.

FIND("#",A1)+1

Find's syntax is as follows:

Find(Find_text, Within_text, Start_num)

In our example, we are looking for the # sign in A1. (We don't use the
"Start_num" as it is not required.) That is the 14th character. Then 1 is
added to arrive at 15.

Incidentally, in the formula bar you can highlight portions of a formula and
hit the F9 key to see the portion evaluated. It is helpful when trying to
debug or understand.

Now the replace function looks like this:

Replace(Old_text, Start_num, Num_chars, New_text)

So our prior expression of

=REPLACE(A1,FIND("#",A1)+1,255,"")

can be written as

=REPLACE(A1,15,255,"")

In words, that says to replace characters in cell A1, starting at the 15th
character, continuing for 255 characters, and you want to replace the old
characters with "" (blank).

I am not sure why 255 was chosen, perhaps the max number of characters in a
string in a cell? I am not sure. But that is my interpretation of the
formula.

Any and all corrections welcomed.

Regards,
Kevin
 
K

Kevin Stecyk

dalstar,

Just to clarify...


So our prior expression of

=REPLACE(A1,FIND("#",A1)+1,255,"")

~~>>>> can be (written as) should be interpreted as...

=REPLACE(A1,15,255,"")

The formula must used as it was originally given in order for the formula to
work on A1:A100 (or whatever the last cell is). The "15" is just to
simplify for our one example.

Regards,
Kevin
 
M

Myrna Larson

In addition to the other suggestions, you can use the formula

=LEFT(A1,FIND("#",A1))

This will keep the pound symbol, but you said you wanted to remove everything AFTER the symbol.
 
K

Kevin Stecyk

Myrna,

That is very good!

I like the very clean approach.

Regards,
Kevin

Myrna Larson said:
In addition to the other suggestions, you can use the formula

=LEFT(A1,FIND("#",A1))

This will keep the pound symbol, but you said you wanted to remove everything AFTER the symbol.


Click to expand...
 
F

Father Guido

Likewise, if you don't want the '#' sign, you can use...

=LEFT(A500,FIND("#",A500)-1), or
=LEFT(A500,FIND("#",A500)-2) if you want to eliminate the
space before the '#' sign as well

Thanks for the explanation!

Norm


Myrna,

That is very good!

I like the very clean approach.

Regards,
Kevin

Myrna Larson said:
In addition to the other suggestions, you can use the formula

=LEFT(A1,FIND("#",A1))

This will keep the pound symbol, but you said you wanted to remove everything AFTER the symbol.




Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good
Click to expand...
Click to expand...
 
R

Robin

Father said:
*Likewise, if you don't want the '#' sign, you can use...

=LEFT(A500,FIND("#",A500)-1), or
=LEFT(A500,FIND("#",A500)-2) if you want to eliminate the
space before the '#' sign as well

Thanks for the explanation!

Norm


Myrna,

That is very good!

I like the very clean approach.

Regards,
Kevin

Myrna Larson said:
In addition to the other suggestions, you can use the formula

=LEFT(A1,FIND("#",A1))

This will keep the pound symbol, but you said you wanted t
Click to expand...
remove
everything AFTER the symbol.
Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good *
Click to expand...


Try this simplest approach:
press ctrl+H
type #* in "find what" text box
leave "replace with" blank
click replace of replace all button.

thank
Click to expand...
Click to expand...
Click to expand...
 
R

Robin

Javi said:
*Is there a way to remove everything in a field after a certai
character using excel or anything else.

It's from a product datafeed that has this format:

Product name #85423
Product Name2 #84216
Product Name3 #51354
etc..

I want to get rid of everything after #

TIA,
Javi :) *
Click to expand...
orange

you can use replace command on edit menu
type #* in find what text box
leave replace with text box blank
click replace or replace all button

have fu
 
Top