Removing everything after a character

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 :)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

0
11/9/2003 11:54:11 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
561 Views

Similar Articles

[PageSpeed] 34

Hi Javi

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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Javi" <Javi.wmktm@excelforum-nospam.com> skrev i melding
news:Javi.wmktm@excelforum-nospam.com...
>
> 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 :)
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
innocent (844)
11/9/2003 12:09:34 PM
On Sun, 9 Nov 2003 06:54:11 -0500, Javi <Javi.wmktm@excelforum-nospam.com>
wrote:

>
>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 :)
>
>

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

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


--ron
0
ronrosenfeld (3122)
11/9/2003 12:37:20 PM
Ron Rosenfeld <ronrosenfeld@nospam.org> wrote in message
news:r6dsqvgbtimqda23m71n1nkqb6lo4o5icf@4ax.com...
>
> In addition to Harald's method, you could use a formula approach:
>
>   =REPLACE(A1,FIND("#",A1)+1,255,"")

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


0
dalstar (3)
11/9/2003 2:30:47 PM
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




"dalstar" <dalstar@ev1nospam.net> wrote in message
news:vqsjvge6ju1268@corp.supernews.com...
>
> Ron Rosenfeld <ronrosenfeld@nospam.org> wrote in message
> news:r6dsqvgbtimqda23m71n1nkqb6lo4o5icf@4ax.com...
> >
> > In addition to Harald's method, you could use a formula approach:
> >
> >   =REPLACE(A1,FIND("#",A1)+1,255,"")
>
> Can you explain the processing of this formula...I can see it works, but
not
> sure how (what are the different functions)
>
>


0
stecyk (172)
11/9/2003 4:17:49 PM
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

"Kevin Stecyk" <stecyk@nohotspammail.com> wrote in message
news:eMP6V0tpDHA.3320@tk2msftngp13.phx.gbl...
> 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
>
>
>
>
> "dalstar" <dalstar@ev1nospam.net> wrote in message
> news:vqsjvge6ju1268@corp.supernews.com...
> >
> > Ron Rosenfeld <ronrosenfeld@nospam.org> wrote in message
> > news:r6dsqvgbtimqda23m71n1nkqb6lo4o5icf@4ax.com...
> > >
> > > In addition to Harald's method, you could use a formula approach:
> > >
> > >   =REPLACE(A1,FIND("#",A1)+1,255,"")
> >
> > Can you explain the processing of this formula...I can see it works, but
> not
> > sure how (what are the different functions)
> >
> >
>
>


0
stecyk (172)
11/9/2003 4:28:08 PM
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.


On Sun, 9 Nov 2003 06:54:11 -0500, Javi <Javi.wmktm@excelforum-nospam.com> wrote:

>
>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 :)
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from http://www.ExcelForum.com/

0
myrnailarson (145)
11/9/2003 10:21:12 PM
Myrna,

That is very good!

I like the very clean approach.

Regards,
Kevin

"Myrna Larson" <myrnailarson@chartermi.net> wrote in message
news:ccftqvk23ildu365pfnjbv6afege4t1b89@4ax.com...
> 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.
>
>
> On Sun, 9 Nov 2003 06:54:11 -0500, Javi <Javi.wmktm@excelforum-nospam.com>
wrote:
>
> >
> >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 :)
> >
> >
> >------------------------------------------------
> >~~ Message posted from http://www.ExcelTip.com/
> >~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
stecyk (172)
11/10/2003 1:28:49 AM
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


On Sun, 9 Nov 2003 18:28:49 -0700, "Kevin Stecyk"
<stecyk@nohotspammail.com> wrote:

>Myrna,
>
>That is very good!
>
>I like the very clean approach.
>
>Regards,
>Kevin
>
>"Myrna Larson" <myrnailarson@chartermi.net> wrote in message
>news:ccftqvk23ildu365pfnjbv6afege4t1b89@4ax.com...
>> 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.
>>
>>
>> On Sun, 9 Nov 2003 06:54:11 -0500, Javi <Javi.wmktm@excelforum-nospam.com>
>wrote:
>>
>> >
>> >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 :)
>> >
>> >
>> >------------------------------------------------
>> >~~ Message posted from http://www.ExcelTip.com/
>> >~~View and post usenet messages directly from http://www.ExcelForum.com/
>>
>


Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good
0
FG (9)
11/10/2003 4:21:46 AM
Reply:

Similar Artilces:

French character lost during convertion
I have a dbase III+ file. There are French characters in it. When I convert the dbase file to Excel 2003, the French characters are changed to wrong characters. For example "Th=E9r=E8se" became "ThTrFse". How can I avoid this? TIA Gordon. ...

Count the number of characters created by string text
Is there a formula that will count the number of characters, and spaces between characters, in a short paragraph created by a string text? It is acceptible if spaces cannot be counted. Thanks. Michael Answered in another group you posted to. In article <1169220981.833905.146790@m58g2000cwm.googlegroups.com>, mrlanier@hotmail.com wrote: > Is there a formula that will count the number of characters, and spaces > between characters, in a short paragraph created by a string text? It > is acceptible if spaces cannot be counted. Thanks. > > Michael Thanks. Michael ...

List of XPath Escape Characters
In my previous post, I asked about a routine which prepares a string for an XPath query by taking care of escape characters. Unable to find a list, I'm now wondering assumign I enclose the attribute value in quotes in my XPath query, what other escape characters need to be handled aside from a quotation mark? As I understand it, an apostrophe wouldn't be a problem since I'm enclosing the string in quotes. thanks! Hi Matthew, For a list of these special characters, you may refer to: HOW TO: Locate and Replace Special Characters in an XML File with Visual Basic .NET http:/...

Removing Synchronised Contacts
We've a number of client pcs that have been installed with the Outlook Client pointing to a user acceptance test (UAT) system. The Outlook client has synchronized CRM contacts into their Outlook Contacts folder. How can we remove the UAT contacts? I tried to do a Find in Outlook to find the CRM specific contacts, but you don't seem to be able to find the CRM user defined fields? Strange. Any thoughts? You can see the custom CRM fields on an contact view. Choose a contact view that lists the records in a table form, such as 'Phone List'. Then, choose to customize ...

OE removed access to the following unsafe attachments in your email
I cant read most of my emails because I keep getting an error message: OE removed access to the following unsafe attachments in your email I know that these emails are safe Can anyone help. Pleeeeeze Thanks in advance Open OE and select Tools | Options | Security tab | uncheck second box from top concerning attachments. "Andrew" <anonymous@discussions.microsoft.com> wrote in message news:ef3501c43d46$d82c8d60$a501280a@phx.gbl... > I cant read most of my emails because I keep getting an > error message: > OE removed access to the following unsafe attachments in &...

Viewing Control Characters
Hi, I want to view and edit control characters such as <tab> and <LF> in text files that I import into Excel 2007 on a Windows XP laptop. I'm not able to view or edit them even though I can verify their presence using LEN and CODE functions. It works on Excel 2007 on an XP desktop at work. The control characters show up as boxes or boxes around symbols. Is there a setup option on XP, Office, or Excel that I need to select? Thanks, Paul Try the Terminal font. For example: =CHAR(1) should display a smilelyface. -- Gary''s Student - gsnu201001 ...

cluadmin won't allow removal of EVS
hey everyone.. got another cluster to retire. this one won't let us remove the EVS, says that one or more users are using a mailbox on the server (error c103f492). the mailboxes & pf store have been removed (were empty), i have gone through article 822931 and verified that the server is not configured to have any PF's, OAB, OAL, free/busy, org forms, rus, is not routing master, not using srs, and doesn't have any connectors. also went through article 279202 to try to find what in AD is telling the server it thinks it has a mailbox there. used ADUC, ldp, ldifde and com...

Access 03 Removing Records during Query that appear to be duplicat
I am working on a report for my company, and I am linking tables to our accounting software and all information is coming through on the tables, but when I create a query to narrow down the information, Access is removing certain records that appear to be duplicate information. This is not the case however, and I am wondering if anyone knows where I can look to turn this feature off, if it is a feature that is on? Please help!!! Queries don't remove records unless it's a delete query. Rather they don't return records that don't meet the criteria. Therefore we need to k...

Unable to remove or add files to WMP on Windows 7 x64
Hi, Recently, I have found that I am unable to add or remove video files to or from Windows Media Player on Windows 7 Professional x64. The main issue I have is with adding new .MP4 files. I can still add and remove music files to and from Windows Media Player. I have set up all my folder permissions correctly. I have added all the relevant files to my Videos Library. Windows Media Player can still play .MP4 files. It is the default player for this format. My full system specs. are included below. Thank you. -- BadHead :) -- OS: Windows 7 Professional (6...

Removing Connectors, IMS and restoring MS+ F/B after moving
I am using MS Exchange 5.5 SP4 and I have migrated all my mailbox to new server. I just forgot to remove these Connection services, the Connector for cc:Mail(<previous_server>), Internet Mail Service(<previous_server>), MS Mail Connector(<previous_server>), and moving the Microsoft Schedule+ Free/Busy Connector (<previous_server>). for the Connections, is there a way that I can remove it manually? if yes, how? And for the F/B connector, shall I remove this? if yes, how? Hoping for your response. regards, Mark What do you mean you forgot to remove it? Before you...

Cannot remove form data from headers.
I often header word documents with the page number followed by the number of pages in the document as auto-entered text by clicking on the icon in the header toolbar. In a current document, I am unable to remove the "number of pages in document" text. Selecting and cutting (cmd-x) has no effect. Placing the pointer just before the text and forward deleting selects the text without deleting it. Selecting it as above, using right arrow to find the end of it and deleting back with the delete key causes the text to vanish for about 5 seconds then return. This has happened to me before...

Want to remove Public Folder store/replicas but see many logons
Hello, I would like to remove a Public Folder Store from a server in my all-Exchange 2003 environment. We have had two Public Folder Stores, but I'd like to use one of those Stores for a Private Store for mailboxes and also because replication between the two servers is problematic. (It's only a few hundred megabytes of infrequently-used data.) I removed the replicas for all Public Folders in the ESM (View Public Folders) then removed replicas for Free/Busy, OAB, and Organization Forms after right-clicking and selecting View System Folders. Still, users showed up under the &q...

way to check if com addin is available before starting outlook and making sure user does not remove it?
for security reasons I need to make certain outlook does not start without com addin and that user does not try to unload it from outlook. Ideas? You can write code to check the COMAddins collection for your COMAddin object of interest and then check the .Connect Boolean property of that COMAddin object. The COM addin can also use the On_Disconnection event and see which method was used to disconnect the addin. If RemoveMode = ext_dm_UserClosed then the user disconnected the addin. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Lead Author, Professional Outlook 2000 Programming,...

Is there a way to prevent Character to Number conversion
I use a web application that displays data in HTML tables in a we browser. I would like to save the HTML files locally, and open them i Excel. Excel takes all of the fields which contain numbers, and coverts the to numeric fields, even if they are intended to be character fields. This causes a problem for me - in that Excel will trim leading zero (from labeler codes, and UPC's) - which I *don't* want to happen. Is there an option or setting that I could use in Excel that woul prevent it from doing this conversion when I open these HTML files? Thanks in advance -- Message posted f...

How do I remove electronic business cards from the 'insert busine.
I have electronic cards added by mistake in the dropdown list. I would like to undo them. ...

Removing zeros from in front of imported data
I am attempting to compare data from two different databases, using excel. The databases contain insurance policy numbers. In one database some of the policy numbers contain two additional zeros at the beginning of the number. In the other database the zeros do not appear. I'm trying to remove just the two leading zeros from the column of imported data so that I can then compare the duplicate entries. I have attempted to select the range of data and then select "Format" "Cells" and select "Number"... Which I thought should remove the leading zeros. Unfortunate...

98/2000 Security Patch Removal
I had outlook 98 and installed the MS security patch. This caused an error everytime I access my contacts either directly or through sending an email. I installed office 2000 and outlook 2000 over this and the error persists. I have outlook v9.0.02711, pre built in security patch. I use the workgroup setup. I am not opposed to security for attachments but I find the error message which is non-fatal very annoying. How can I remove the security patch? Easily. If I uninstall and reinstall office 2000 , will profiles be preserved with the uninstall? Can they be saved? Interesting that I still ...

Find file and remove .csv extension
Hi, I'm tring to open a window, select a file with CSV extension, the file is coded to SelectedFile and then remove I want to remove the .CSV before renaming the file with the same name albeit with a .TXT extension. I've tried the code below for the inital test but it fails on the WorksheetFunction code. Am I on the right track? Thanks, Rob Sub macro1() Dim Filter, Caption, SelectedFile, DestinationFile As String Filter = "Text files (*.csv),*.csv" Caption = "Please Select a File " & TheUser SelectedFile = Application.GetOpenFilename(F...

Paid trx removal SQL error
When running Paid Transaction Removal, We receive a message: (Microsoft)(SQL Native Client)(SQL Server)Cannot insert duplicate key row in object 'dbo.RM30101' with unique index 'AK3RM30101'. After clicking OK, we receive the following message: The stored procedure rmPaidTransactionRemoval returned the following results: DBMS: 2601, Microsoft Dynamics GP: 0. I recreated the RM keys but this error showed up again. Also haven't been able to find anything through partnersource. Thanks for your help! What this is telling you is that you have a document in RM20101 that ...

special characters in a visio drawing
How do I enter special characters in a drawing (infinity symbol) On Fri, 8 Jun 2007 06:19:04 -0700, Lincoln B <LincolnB@discussions.microsoft.com> wrote: >How do I enter special characters in a drawing (infinity symbol) a. Use Character Map from Start -> Accessories -> System b. (only while in text entry mode): menu Insert -> Symbol c. Various 3rd party programs -- Regards, Paul Herber, Sandrila Ltd. http://www.pherber.com/ Unicode characters http://www.diacrit.sandrila.co.uk/ Email address in headers is invalid. "Lincoln B" wrote: &...

Problems with update
I am also having problems with the 12.1.7 update. I am using Office 12.1.5 with 10.5.6. I am not running the beta software and I did not use any utilities to remove any languages (nor did I manually remove them). I run a pretty basic system. I am running a 24" Intel iMac. I receive the error "You cannot install Office 2008 12.1.7 Update on this volume. A version of the software required to install this update was not found on this volume." Microsoft needs to come up with a better solution than removing office and reinstalling office. <tlassek@gmail.com> wrote: > I...

Routing Recipient feature removed from Excel 2007??
The company I work for have a small majority of users who utilise Excel 2003's Routing Recipient feature for an expense reporting workflow process. I've recently heard that this feature (Routing Recipient) has been removed from Excel 2007... is this true?? It seems so -- Regards, Peo Sjoblom "chrsta" <nospam@nospam.com> wrote in message news:ExZFi.204158$p7.159805@fe2.news.blueyonder.co.uk... > The company I work for have a small majority of users who utilise Excel > 2003's Routing Recipient feature for an expense reporting workflow > proc...

uninstalling Exchange and removing all traces of Exchange AD attri
Hi All, I am having much difficulty with my Exchange installation. This all happened after I installed 2007 and it was not a pleasant experience at all. Now I have multiple administrative groups so I removed the ones I didn't want any more with adsiedit.msc. Now my Exchange 2003 isn't creating mailboxes for users even though it says it successfully completed them. I also cannot send or receive but it's not reporting any errors at all. I am perplexed and think it would just be easiest to nuke the whole thing and start over. I just don't want to have to re-setup my domain...

Find out if a Unicode character is a letter?
Hi guys! Does anybody know if there's a way in c++ (STL or MFC) to find out if a Unicode character is categorized as a letter? I need something that is pretty much like Char.IsLetter() from .NET. I've seen the MSDN about it, and it seems to find characters under categories "Lu, Ll, Lt, Lm, Lo". Is that something that I can check or is it some .NET built-it definition of Unicode? Thanks! "Dani" <anonymous@discussions.microsoft.com> wrote in message news:uGEUCsE0HHA.3400@TK2MSFTNGP03.phx.gbl... > Hi guys! > Does anybody know if there's a way ...

Removing a server, need mail.
I have a question i hope someone can answer for me. I am removing a server from someones home (server 2000). There are 2 workstations connected to this server and the mail is stored on the server (in exchange i believe). The person no longer wants a server setup to make things easier for him to deal with. I need to get his mail, contacts, ect. off the server before i take it down. Is there an easy way of doing this so that I wont lose anything. I want to setup local POP on his machines and just use psts. Any help would be great. If it is in fact Outlook/Exchange, just export the data in...