Edit No Format

I have a spreadsheet that Has a column of No's that look like this AB 12 45 87 B the column has about 30,000 No 's in it. I want the No's to look like AB124587B so that they do not have the spaces in, any way of doing this un excel.
0
anonymous (74722)
1/2/2004 12:26:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
641 Views

Similar Articles

[PageSpeed] 5

If it's always the same format you can do it by using a combination of
the left(),mid(), and right() functions.. Then use the & sign to fix
it....

example : 

a1: ab 12

a2: left(a1,2) & right(a1,2)

a2 then says: ab12

there might be a function that just removes all spaces though... trim()
only works for opening and ending spaces.


---
Message posted from http://www.ExcelForum.com/

0
1/2/2004 12:34:57 PM
Select the column, then

Edit / Replace / 'replace what = a space (ie hit the space bar once), 'replace
with' = blank (ie leave it blank)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
 Seasons Greetings and Very Best wishes to all  :-)
----------------------------------------------------------------------------



"Mani" <anonymous@discussions.microsoft.com> wrote in message
news:55E628FD-82E0-4CB5-BE6D-ADDBDA803637@microsoft.com...
> I have a spreadsheet that Has a column of No's that look like this AB 12 45 87
B the column has about 30,000 No 's in it. I want the No's to look like
AB124587B so that they do not have the spaces in, any way of doing this un
excel.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.557 / Virus Database: 349 - Release Date: 30/12/2003


0
ken.wright (2489)
1/2/2004 1:12:50 PM
>-----Original Message-----
>I have a spreadsheet that Has a column of No's that look 
like this AB 12 45 87 B the column has about 30,000 No 's 
in it. I want the No's to look like AB124587B so that they 
do not have the spaces in, any way of doing this un excel.
>.
>
Mani

Change the references to the following code as neccessary.
It is written to work on column B from row 2

Option Explicit

Dim r As Long, col As Integer

Sub clearSpaces()
Dim rng As Range
Dim c
Dim n As Integer, l As Integer
  Range("B2").Select  'Change this to suit it should be 
the column with part numbers
  r = Application.WorksheetFunction.CountA(Range("B:B"))
  col = ActiveCell.Column
  Set rng = Range(Cells(2, col), Cells(r, col))
  For Each c In rng
  c.Replace What:=" ", Replacement:="", LookAt:=xlPart, 
SearchOrder:= _
        xlByRows, MatchCase:=False
  Next c
End Sub

Regards
Peter
0
1/2/2004 4:22:21 PM
Reply:

Similar Artilces:

How to format a cell from date to week?
Dear all, How to format a cell date like "2009-12-10" to year /week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 to 201002 = 5 weeks? BR//nginhong You cannot achieve this with cell formatting. Excel does not support weeks in a date format. To change a date to yyyyww, use: =year(a1)*100+weeknum(a1) The number of weeks difference between two of these cells, use: =(int(a2/100)-int(a1/100))*52+(mod(a2,100)-mod(a1,100)) This calculates to 4 weeks, not 5, just as the difference between 201001 and 2010...

Editing Text when using Publisher as a web page...
I'm using Publisher to document the status of current taskings in the office. I'm trying to ger Publisher to allow people that pull up the web-page to add and edit text on the web page so we can keep everything up to date. Right now, someone has to come to me and tell me the changes, then I change the info in the Pub doc, then I have to repost the web-page. Is there a way for people to edit the text on the web-page when they pull it up. I have text boxes in there now that people can edit, but the text doesn't update when you close the page and re-open it. Please Help if you ...

cell format #11
Hello, Can anyone help? If i export from Access to Excel i can get the cell in Excel to look like this: Joe Bannister 123 Holly Lane Spa England The cell format is general with wrap text. Does anyone know how to do this in Excel without exporting from Access? I've tried wrapping text, different formats etc but nothing works. One thing i did notice is that when i right clicked the cell and chose format cells the content of the cell was the whole text but with square boxes seperating the parts as above. Any help appreciated. Cheers Joe If you're trying to type more than...

SQL Server 2000 Standard Edition vs SQL 2000 Personnel Edition
I am using SQL server 2000 standard Edition sp3 with Gp 9 Sp3, I am planning to move GP with database to new server different hardware. can I use sql 2000 personnel edition sp3 or should I use same old version. I hope u understand my question. MK KSA Generally speaking, personal editions are not robust enough to support a product like GP. I would stay away. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "MK KSA&quo...

Custom Format #4
I am trying to do a custom format so that what is entered in the cell will automatically enter a dash. Example: T1-0504 All they will enter will be: T10504 What format do I need to enter. Thanks Hi this is not possible as you don't enter a value. But if the user enter 10504 (without a leading character) you could use the custom format "T"0-0000 -- Regards Frank Kabel Frankfurt, Germany Denise wrote: > I am trying to do a custom format so that what is entered in the cell > will automatically enter a dash. > > Example: > T1-0504 > > All they will enter ...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

format a cell as running time
Hello All, I have a simple question I cannot figure out. I would like to keep a spreadsheet of my workouts(running) . I have columns "Total Time" and Split Time(s)" (multiple of these) How would I format a cell as running time? i.e. 22.20.93 and say one of the splits is 2.28.03 (two minutes etc...) Thanks for any suggestions, Homer One way: Format/Cell/Number/Custom mm:ss.00 then enter 2:28.03 In article <94D844C9-A8C9-42FC-91DF-BCDD252ACC6E@microsoft.com>, "Homer" <Homer@discussions.microsoft.com> wrote: > Hello All, > I hav...

editting auto recovery
Hello, is it possible to change the auto recovery directory just for one document and is also possible to ensure that the auto recovery file is not deleted after the file is closed? Cheers Hi AFAIK this is not possible. But you may consider using the following add-in: http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) -- Regards Frank Kabel Frankfurt, Germany Hcoms wrote: > Hello, > > is it possible to change the auto recovery directory just for one > document and is also possible to ensure that the auto recovery file > ...

Conditional formatting
I've followed the correspondence on conditional formatting on charts with interest but is it possible to conditionally format a line chart i.e. green above a certain value, red below that value? -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096 View this thread: http://www.excelforum.com/showthread.php?threadid=529599 Hi, Yes it's possible. http://www.andypope.info/charts/conditionalline.htm Cheers Andy Brisbane Rob wrote: > I've follow...

BOM & Edit List
Hi, I created an Assembly transaction and printed it from the Assembly transaction entry window before posting it. In the posting journal that prints it does not print the Assemble cost, extended assemble cost, Unit cost of the components and the extended cost. But if i post the transaction then the same posting journal is printed with the costs. Why does it not print? Please let me know Thanks Dev It only pulls posted costs, like other Great Plains edit lists including items. You will need to modify the report to include costs. "Dev" wrote: > Hi, > > I created...

template edit question, add more? edit template?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel i'm using a resume template and it's beautiful, but only allows four job positions before it moves one the education, etc. <br><br><i>guess theres a way to do it on the pc version, but with mac, how do i insert an addition formatted block? it will only let me work within the template currently.</i>&#32;<br><br><i>hope that makes sense, thanks for the help!</i> Mac or PC makes no difference � it's a matter of how the template is constructed. With...

How to edit entitys attributes directly?
I messed up with entity mappings and ended up with errror: attribute not found. How can I manually remove attribute from entity? Tried export -edit -import but it seems not to be the right way. You will first need to remove the "bad" mapping that you created. Since it is referncing the field, you need to do that prior to trying to remove the field. You also need to remove it from any forms & views that may be displaying it. The customization Import will not remove fields (as you have discovered). That is done as a safety feature to rpevent accidental loss of data (ima...

HELP: Conditional Formatting Based On A Sub String
Hello, I need some help on trying to apply some conditional formatting based on sub string within string contained within a given cell. For example; CELL A1 contains "cat.dog.elephant.parrot.girrafe.duck" I want the cell to be Green if the string contains the sub string "dog". I haven't a clue how to archive this. Looking at the excel functions i don't see a InStr. Any ideas? Hardeep. In the conditional format box change the "Cell value is" to "Formula is and type this formula: =search("dog",A1)>0 then set your format to gree...

prob with conditional formatting formula
I am basically familiar with Excel 2000 but still a novice. This is th formula that I am trying to conditional format. =IF(SUM(B7:K7)=0,N6+M7-T7,(SUM(B7:K7)+M7+N6-T7)). If after seeing thi so far, your surprised that it works so am I. B7 through K7 ar various income columns. N6 is the previous CASH balance, M7 is anothe cash income column which is not taxed and T7 is a CASH payout column. Question - How do I write the formula in Cond. Format. to show a blan or invisible entry in column N6 if there is no activity in B7:K7 or M7 T7. I can only get part way there with =IF(SUM(B7:K7)=0,N6). ...

Editing
Hi! Could you please tell me how to remove a linked transaction within the budget planner. For example, I have a double entry under the debt category. ...

Macro to edit details
Hi, i am currently trying to create a macro regarding editing staf details on a worksheet....... therefore i recorded a macro and named it editstafflist, i highlighte my headings...and then went to 'data > form' and then had to close th form and then stop my macro. It wouldnt allow me to stop my macr whilst the dialogue box was open. Anyways after stopping the macro drew out a button and assigned this 'editstafflist' macro to it. saved my sheet and then clicked on the button, but a error '1004' kep occuring, can anyone please help me on this as i am rather stuck ...

editing a drop down #2
that's not one of my choices. -- kateweb ------------------------------------------------------------------------ kateweb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16301 View this thread: http://www.excelforum.com/showthread.php?threadid=276931 what do you mean by this .. is it greyed out on the toolbar or shows "any value"? if you'ld like to tell me where you downloaded it from i can go have a look at it cheers JulieD "kateweb" <kateweb.1fictb@excelforum-nospam.com> wrote in message news:kateweb.1fictb@excelfor...

Setting caret in edit control & activating it.
Hello Does anyone have any example code for setting and enabling the caret in an edit control ? Nicholas Take a look at the CEdit::SetSel() member function. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Nicholas Kingsley" <nickk@nildram.co.uk> wrote in message news:3f9d418a$0$106$65c69314@mercury.nildram.net... > Hello > > Does anyone have any example code for setting and enabling the caret in an > edit control ? > > Nicholas > > If you are asking how to create & use your own caret in an edit control then check...

Possible conditional formatting?
cell A1="new" cell B1="new" cell C1="new" cell D1="comp" cell E1="ABCDE" Within conditional formatting, how do I go about filling E1 in green if any or all of the A1:D1 range is populated with "comp", keeping the narrative in E1 "ABCDE" All help much appreciated Thanks, Gary Under CF, choose Formula Is and use: =COUNTIF($A1:$D1,"comp")>0 HTH Jason Atlanta, GA >-----Original Message----- >cell A1="new" >cell B1="new" >cell C1="new" >cell D1="comp" &...

Data collection Form Edit
We have a form which will be used almost daily, for data collection, based on a query. During the test we found a typo in one of the field labels where a brief description is included. Is there some way to edit the form? (recreating it with all the custom lables is just too long a task). Another issue on this topic, every time the form is sent, the messge reverts to the default "Please fill out the form and retunr it to me". Can this default message be changed? Nothing in the help functions. Any advice, or deirection to a learning resource will be appreciated. Thanks is adva...

compatibility with document formatted in Word for Windows.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I can't make my A5 document print on A4 paper as two pages per page. Print preview insists that part of the text is beyond the margins. I'm new to mac so may be missing a vital step. Does it work in Windows? Do both the Windows PC and the Mac connect to the same printer? This may very well be a printer issue, with the printer connected to your Mac using a pre-defined document margin that is larger than the one used by the printer connected to your Windows PC. On 26/03/10 3:09, Jenny_Missen@officeformac.com wrot...

Having Insert button functionality while editing
Hi, I want to have the functionality of Insert button (on keyboard) in my while application while editing in a cell or a edit box. For eg if i am editing my text say "alpha" and my cursor is between "l" and "p" and I insert an alphabet say "z", i want the "p" should be replaced by "Z" and the text should become "alzha". Please suggest. Thanks in advance. Regards, Arjun CEdit does not support that by default. Here is a good example of how implement this (Although I think UpdateCaret should just be a method of CXEdi...

Change date formats on cheque
We are using payable cheques with the stub on the top. We are trying to modify the date to ready dd-mmm-yyyy. I have followed the instructions in https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;871918 except to change the table name becuse I am on 8.0. This will print the month format as "september" what I am looking to do is have it read the alpha short form of the month (ie - Jan, Feb, Mar......) Is there any way to accomplish this? It currently is working and printing the full month. I am just interested in getting the short form of the month to print. ...

Format Question 10-01-07
How do I get this text box to format $#,###.## at the moment I am getting Grand Total:1085.5 instead of Grand Total $1,085.50 = "Grand Total:" & (NZ(DLookUp("Payable","qPayableTotalForPayment","OwnerID =" & tbOwnerID.Value),"")) Thanks for any Help...............Bob Simple method - in design view format the control as currency HTH -- Wayne Manchester, England. "Bob V" wrote: > > > How do I get this text box to format $#,###.## at the moment I am getting > Grand Total:1085.5 instead of Grand Total...

How do i change column header from R[1] C format to A1 format?
I am not sure how to get the worksheet to show alpha in the columns and formulas instead of numeric. Hi, Tools|Options - general tab and uncheck R1C1 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "lovnlife" wrote: > I am not sure how to get the worksheet to show alpha in the columns and > formulas instead of numeric. Hi, Press CTRL + ~ ~ is at the right of number 1 "lovnlife" wrote: > I am not sure how to ge...