Text to Column #4

Hello,

I have the following text in column A, which is one line:

:! 10428712  ! NAME LASTNAME           ! 20092004 ! 00458921 ! PMIAVW   !
Is it possible with a VBA macro to create a column everytime there's "!" and 
to delete ":" 
Thanks for any help,

0
Jeff1 (635)
11/18/2004 1:52:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
920 Views

Similar Articles

[PageSpeed] 11

You're going to have to find out what those characters are.  

I like Chip Pearson's addin:  CellView
http://www.cpearson.com/excel/CellView.htm
to tell me those funny characters.

Then you could use a macro to clean them up:

Option Explicit
Sub cleanEmUp()

    Dim myBadChars As Variant
    Dim iCtr As Long
    
    myBadChars = Array(Chr(yy) & Chr(zz))
    
    For iCtr = LBound(myBadChars) To UBound(myBadChars)
        ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next iCtr
    
End Sub

replace yy and xx with what Chip's addin shows.

And then record a macro while you 
select the column
data|text to columns, delimited by !

And you'll have your code.



Jeff wrote:
> 
> Hello,
> 
> I have the following text in column A, which is one line:
> 
> :! 10428712  ! NAME LASTNAME           ! 20092004 ! 00458921 ! PMIAVW   !
> Is it possible with a VBA macro to create a column everytime there's "!" and
> to delete ":"
> Thanks for any help,

-- 

Dave Peterson
0
ec357201 (5290)
11/19/2004 1:19:09 AM
Reply:

Similar Artilces:

converting "text" numbers to numbers
I have been using the technique of multiplying cells that have number entries that behave like text entries by the value of 1 to convert them to numbers. However, some mixed cell entries, convert strangely. For example the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1. Any explanation? Any ideas on how to solve other than checking cell by cell to confirm all characters are numbers? Confused! Frustated! <grin>. Bob Just figured out the "why". An entry like 6 A is converted to 6AM. Its numerical value is 0.25. Still hoping for a ...

Too much space between text boxes in e-mailed Publisher document
I am using Microsoft Publisher 2003 to make a newsletter to be e-mailed. I have put all of the information in text boxes as close as I can get them without having them touch. (I found out that when making them touch, it causes the entire document to become a jpg when e-mailed and then cannot be printed by the receiver.) When I put the text boxes as close as they can get when I am looking at it and then I send the document as an e-mail, the e-mail shows huge amounts of space between the text boxes instead of keeping them close together. How can I fix this? I have looked at all the &...

to make column letter dynamic in charts
I know there have been previous posts on this but I am relatively new to VBA and am having trouble adapting it to my needs I have many charts in a sheet. i want to make their souce data dynamic. the source range of charts are like sheet1!$B$9:$CN$13 where $B$9 is starting range and " $CN$ " is ending range. for every chart the starting range is different but the ending Datarange is of same Column "$CN" . i want to make the column Letter dynamic ,Row number are Static ... from "CN" it has to change it to "CO" in all the charts.. Every occur...

Find/delete text string
Somebody emailed a 1MB workbook with some basic charts and some big pivot tables/charts. I am supposed to remove all mention of the company name, "COMPANY", from the workbook. Normally I wouldn't be afraid - but how can I ensure I remove all references to "COMPANY" from the pivot tables too? thanks in advance, vinnie Vinnie, I don't know whether this does it, but Jan Karel Pietrese's FlexFind utility which you can get at which you can get at http://www.bmsltd.co.uk/mvp/ is probably the most powerful tool for this type of thing. -- HTH Bob Phillips &quo...

Formatting in columns
Hello. I have a report that lists name and phone extension across multiple columns in a report. Is it possible to insert dots between the name and the extension? They are two separate fields from a query. So far I have been unsuccessful. Many thanks for any help. Bird Byte wrote: >Hello. I have a report that lists name and phone extension across multiple >columns in a report. Is it possible to insert dots between the name and the >extension? They are two separate fields from a query. So far I have been >unsuccessful. > Not a simple problem. There are two...

Repeating Column Headers And Blank Worksheets With Fomulas
I have two questions about excel: In my spreadsheet I have titles for all my columns that I have typed in the first cell of every column and I was wonder how you make those titles repeat on every page of the spreadsheet. My second question is how do you take an exsiting spreadsheet and then make a new spreadsheet thats blank but have all the formulas as the original one. Thanks Hi 1. Question: - for printing: file - Pagesetup - sheets' and define your repeating rows -for display: Move the cursor below your heading row and goto 'Win...

How do I insert the same extra digit to each cell in a column?
Have column of numbers that must be converted to a new number by simply adding same to number is two specific places. ex: 6200-80 must become 6200-080-3 How to do this w/out re-keying entire workbook? 1. Insert a temporary column next to the one you want to change. (If the information is in A right click on column B and select insert) 2. Type this formula in B1... =A1&"-3" 3. Copy the cell by dragging the bottom right corner all the way down to the end of your list. 4. Highlight column B and select copy. Right click on A select paste special. Select values. 5...

SQL Encode Text String
Hi, Is there a equivalent of Server.HtmlEncode in SQL itself? Thanks No built-in functions, but you could create one with CLR or you could do it in the client using the ASP.NET function you reference. See this post http://www.sqlclr.net/Examples/tabid/55/articleType/ArticleView/articleId/31/Default.aspx for a CLR solution. "MS Forum Newsgroup User" <user@microsoft.com> wrote in message news:uOgoS4HbKHA.5796@TK2MSFTNGP06.phx.gbl... > Hi, > > Is there a equivalent of Server.HtmlEncode in SQL itself? > > Thanks Oops, that was the HTM...

Is CRM 4.0 still coming out this month?
Anyone know if the relase is still on schedule? Please be patient, as soon as it RTM's we will post. ======================= John O'Donnell Microsoft Dynamics ISV Architect Evangelist http://blogs.msdn.com/usisvde "Mark Andrews" <mandrews__NOSPAM__@ddiworld.com> wrote in message news:ejyEUf4NIHA.5860@TK2MSFTNGP04.phx.gbl... > Anyone know if the relase is still on schedule? > > On Dec 6, 12:17 pm, "John O'Donnell" <csharpconsult...@nospam- hotmail.com-nospam> wrote: > Please be patient, as soon as it RTM's we will post. >...

Finding duplicates in column
Hi, I have Excel 2002 and have 6000 emails addresses in a column. How can I find if there are any duplicates in that column? Thanks rock Assuming the data is in column A, add a formula in column B of =IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","") Cpy that down, then you can filter column B for Duplicate Sorry to be so newby Bob, but when you say 'copy' down, what exactly do you mean? I have entered the formula in B1 but.. Thanks rock Bob Phillips wrote: > Assuming the data is in column A, add a formula in column B of > > =IF(COUNTI...

Chart title #4
Hello everybody. How to name the chart (title) with the value in a cell. say B1. Thanks to you all With regards Sridhar Select the chart title, click in the formula bar, & type = then click in B1, & accept the formula. -- David Biddulph "yshridhar" <yshridhar@discussions.microsoft.com> wrote in message news:39D32BA9-7109-4ED2-8F83-E9CE99D3501A@microsoft.com... > Hello everybody. How to name the chart (title) with the value in a cell. > say > B1. > Thanks to you all > With regards > Sridhar Sorry Mr. David. I could not follow you. Chart|c...

Hide row and column labels
How can I hide the row and column headings in a spreadsheet. (the a, b, c, etc. across and the 1, 2, 3, etc. down) Thanks! Hi Pam! Use: Tools > Options > View Remove check from "Row and Column Headers" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au How about programmatically? On Fri, 25 Jun 2004 06:20:20 +1000, "Norman Harker" <njharker@optusnet.com.au> wrote: >Hi Pam! > >Use: > >Tools > Options > View >Remove check from "Row and Column Headers" >OK Record a macro while you...

Conditional Formatting? #4
Excel 2000 ... I have random PNs in Col B that are not unique ... They are sorted (Ascend) & often repeat ... Above said ... I would like to set Conditional Formatting to change pattern color when PN changes. 123456 ... Yellow 234567 ... Green 234567 ... Green 234567 ... Green 345678 ... Yellow 345678 ... Yellow 456789 ... Green 567890 ... Yellow 567890 ... Yellow etc ... As always ... My many Thanks to those of you that are intimate with Excel & support this board ... Kha Hi try: - select your range (e.g. A1:A20) - goto the conditional format dialog and choose 'Formula is&#...

cannot open 4.10 mb publisher document in xp pro
I added two pages to a publisher document and now cannot open the document on my computer--memory? How do I fix this? Thanks for any help. Billph, Are you opening this file in the same version of Publisher and on the same machine? Are you getting an error message? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "billph" <billph@discussions.microsoft.com> wrote in message news:C548590D-493D-4AF2-8CA0-2814840E12F2@microsoft.com... >I added two pages to a publi...

bottom of wrapped text not visible at times
Can anyone help shed light on why at times I can't see the bottom few words or a line of wrapped text in a cell?? How much is not visible seems to depend on at least the zoom setting, the number of words and characters and the column width BUT try the example below. Enter "one two three four five" in a cell. Format it to wrap text. Set the column width to 12 and zoom to 87%. Format | row | auto fit. I can't see the word five ;-( A zoom of 85% and all is OK!! It always prints AOK. The 87% zoom is a little unusual in that its not an 80, 85 or 90% etc BUT numbers like t...

Can I set a default text size and font for text boxes
I have to use lots of text boxes all with the same style and size of font. Can I change the default size to the one that I want without saving and pasting a blank box? Yes you can: Setting Text Defaults using Normal.pub by Brian Kvalheim http://ed.mvps.org/Static.aspx?=Publisher/normal.pub -- Mary Sauer http://msauer.mvps.org/ "Johnr" <Johnr@discussions.microsoft.com> wrote in message news:5670218D-5C8A-45DD-9F78-AED34ADC4429@microsoft.com... >I have to use lots of text boxes all with the same style and size of font. > Can I change the default size t...

CRM 4.0 CTP 3 + Server 2008 RC 1 + SQL 2008 CTP 5
Hi all, I have tried to install CRM 4.0 CTP3 based on Server 2008 RC1 and SQL 2008 CTP5. I have encountered the following error message while registering a new Organization : "Version string portion was too short or too long " I have googled around and found this little information : http://www.microsoft.com/technet/community/chats/trans/sql/07_0611_tn_katmai.mspx Brian Welcker (Expert): Q: I've tried to install the CRM titan CTP with SQL server 2008 ,the reporting services failed to be authenticated with the CRM generating this error "Version string portion was to...

Deleting rows with 0 value in Column X
Hi, I would like to create a macro to delete all rows in my worksheet tha have a 0 value in column X. Any easy instructions, or info on a boo that would point me in the right direction? Thanks, Mik -- Message posted from http://www.ExcelForum.com A little digging in the google archives and code manipulation and I came up with: Sub DeleteRows() Dim iLastRow As Integer Dim i As Integer Application.ScreenUpdating = False iLastRow = Cells(Rows.Count, "X").End(xlUp).Row For i = iLastRow To 1 Step -1 With Cells(i, 24) If .Value = 0 Then .EntireRow.Delete ...

HTML Text retrieval
Am using Joels dump sub to retrieve location of text (a persons date of birth) within a web page, all I get from the dump is: HTML HEAD TITLE FRAMESET FRAME FRAME FRAME I have tried returning Frame.item(0).innertext item1 item 2 etc but get no innertext. any ideas? Not all object have innertext. I suspect the FRAME is really another webpage that the Java code is download and displaying. I would loolk at the source code and see if there is a href property associate with the object. You can add the href into the dump and put the data in a different column. Without ...

My text boxes have shadows, but printing some don't show?
I have a calendar with text boxes. I have put shadows around them but when I print, several boxes print without the shadow around them. I have Pub 2007 If the text boxes are touching it could be a layering problem. See if bringing to the front helps. -- Mary Sauer http://msauer.mvps.org/ "Crafty Lady" <Crafty Lady@discussions.microsoft.com> wrote in message news:69E8B0EB-E169-4179-8935-C300D5F73EBD@microsoft.com... >I have a calendar with text boxes. I have put shadows around them but when I > print, several boxes print without the shadow around them. > > I ...

CONVERT NUMBER TO TEXT E.G 1000 TO "ONE THOUSAND"
I WANT TO CONVERT CURRENCY NUMBER IN REASABLE NUMBER TEXT CONVERSION E.G 1,000 TO "ONE THOUSAND" 2,950 TO "TWO THOUSAND NINE HUNDRED FIFTY" You could use a vlookup, on 1 excel sheet in column A put all th numbers you need to convert ie 100, 200, 300 etc , in column B put i what you would like to convert these into ie, Column A 100, Column One Hundred Then on another sheet say in column B Click on the function bar doubl click vlookup , for lookup_value click on the cell where you will ente data, for table_array highlight the two columns you typed in on th other sheet ...

Single sheet with multi column spacing
Using Excel97 is it possible to place three sets of data on a singl sheet with each set of data using totally unrelated column spacing? Rows 1 thru 8 have specific column spacing. Rows 9 thru 12 hav completely different column spacing. Rows 13 thru 20 have completel different column spacing. If possible, how do you set it up? Thanks, Timo -- Message posted from http://www.ExcelForum.com Timon Column width is a setting for the entire column. Work-arounds are "Center across selection" or "merged" cells. I do not recommend "merged" cells due to the problems...

Series including text possible?
Is there a better way to create a "series" that is based on characters as well as digits than what I'm doing below? I created 3 columns. I put the text in one, the digits in the second that I could apply the series to, with the 3rd column holding a ":". But when I paste into the text file, I have tabs separating the 3 columsn where I was hoping for no space. So this here - NAQAHDAH 01 : NAQAHDAH 02 : NAQAHDAH 03 : should paste like this - NAQAHD6H01: NAQAHDAH02: NAQAHDAH03: NAQAHDAH04: NAQAHDAH05: NAQAHDAH06: NAQAHDAH07: NAQAHDAH08: NAQAHDAH09 Is there perhaps a w...

40 column receipt #2
Hi All, I am customizing my 40 column receipt I want to do something like <ROW> "This Coupon Expires on : " Transaction.Date + 15 </ROW> I would like to see (Transaction.Date + 15) in date format right now it prints some number. How do I do that? OR is their any better way to this? Thanks ...

Need to create a chart with 4 parameters
JAN FEB MAR Oranges Apples Oranges Apples Oranges Apples Adam 2 3 4 5 6 7 Bern 1 7 8 9 1 1 Collin 3 4 5 8 1 1 Dave 5 6 7 1 2 4 How do i draw a chart to show the number of apples & oranges for Adam , Bern , Collin and Dave in the months of Jan, Feb ... in one graph. Please help. -- will appreciate your help Pia, This is what I did. Started at A1 - Blank, B1 - Jan, D1 - Feb, F1 - Mar A2 - Blank, B2 - Oranges, C2 - Apples, D2 - Oranges, E2 - Apples, F2 - Oranges, G2 - Apples A3 - Adam, B3 - 2,C3 - 3, D3 - 4, E3 - 5 F3 - 6, G3 - 7 A4 - Bern, B4 - 1,C4 - 7, D4 - 8, E4 - 9, F4 - 1, G4...