Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
I have data formatted as text, with leading zeroes, such as 00AF234, 014F,
etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Paige,
If you data starts in cell A1, use this formula in B2: =IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2) and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of formulas to remove 4 leading zero characters), so you may have to insert extra columns. Then copy the last column of results and paste as values over the original data, and then remove the columns of formulas. Or select all the cells, and run this macro: Sub RemoveLeadingZero() Dim myC As Range For Each myC In Selection While Left(myC.Value, 1) = "0" myC.Value = Mid(myC.Value, 2) Wend Next myC End Sub HTH, Bernie MS Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Try this array formula** :
=MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Here is a possible solution. A bit ugly but it will work... Add a helper
column to the source data. In this column concatenate a character that you do not find in your text field to the front of your text string. Assuming your text field is in column A then use a formula such as ="-" & A1 Copy that formula down to concatenate the the dash to the front of the text field. Now copy and paste values so that you end up with text that looks like this -00AF234 -014F Now you can do a find and replace looking for -00 and replacing with nothing. Then do -0 replacing with nothing... Like I said a bit ugly but it will work. -- HTH... Jim Thomlinson "Paige" wrote: I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
If you data starts in cell A1, use this formula in B2:
Sorry - I should have said cell A2.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Paige, If you data starts in cell A1, use this formula in B2: =IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2) and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of formulas to remove 4 leading zero characters), so you may have to insert extra columns. Then copy the last column of results and paste as values over the original data, and then remove the columns of formulas. Or select all the cells, and run this macro: Sub RemoveLeadingZero() Dim myC As Range For Each myC In Selection While Left(myC.Value, 1) = "0" myC.Value = Mid(myC.Value, 2) Wend Next myC End Sub HTH, Bernie MS Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Impressive. This is a much better solution than mine...
-- HTH... Jim Thomlinson "T. Valko" wrote: Try this array formula** : =MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Thanks!
It's both an array and volatile but, as Bernie mentioned in his reply, after you get everything processed you can always CopyPaste SpecialValues to convert to constants. -- Biff Microsoft Excel MVP "Jim Thomlinson" wrote in message ... Impressive. This is a much better solution than mine... -- HTH... Jim Thomlinson "T. Valko" wrote: Try this array formula** : =MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
WOW!!!!! You are all awesome! Thanks for responding so quickly. Am going
with the =MID formula.....is the easiest, but keeping the other 2 for reference also cause you never when they might come in handy. I'm just constantly amazed at the brain power out there! Have a good weekend.... "T. Valko" wrote: Thanks! It's both an array and volatile but, as Bernie mentioned in his reply, after you get everything processed you can always CopyPaste SpecialValues to convert to constants. -- Biff Microsoft Excel MVP "Jim Thomlinson" wrote in message ... Impressive. This is a much better solution than mine... -- HTH... Jim Thomlinson "T. Valko" wrote: Try this array formula** : =MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Paige" wrote in message ... WOW!!!!! You are all awesome! Thanks for responding so quickly. Am going with the =MID formula.....is the easiest, but keeping the other 2 for reference also cause you never when they might come in handy. I'm just constantly amazed at the brain power out there! Have a good weekend.... "T. Valko" wrote: Thanks! It's both an array and volatile but, as Bernie mentioned in his reply, after you get everything processed you can always CopyPaste SpecialValues to convert to constants. -- Biff Microsoft Excel MVP "Jim Thomlinson" wrote in message ... Impressive. This is a much better solution than mine... -- HTH... Jim Thomlinson "T. Valko" wrote: Try this array formula** : =MID(A1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)<"0",0),255) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Bernie,
Not only does your macro make this task easy (I had the same problem this thread addresses), but convinced me to used VB macros more. Thanks. "Bernie Deitrick" wrote: Paige, If you data starts in cell A1, use this formula in B2: =IF(LEFT(A2,1)="0",MID(A2,2,LEN(A2)),A2) and copy to the left for as many columns as you might have leading zeros (it takes 4 columns of formulas to remove 4 leading zero characters), so you may have to insert extra columns. Then copy the last column of results and paste as values over the original data, and then remove the columns of formulas. Or select all the cells, and run this macro: Sub RemoveLeadingZero() Dim myC As Range For Each myC In Selection While Left(myC.Value, 1) = "0" myC.Value = Mid(myC.Value, 2) Wend Next myC End Sub HTH, Bernie MS Excel MVP "Paige" wrote in message ... I have data formatted as text, with leading zeroes, such as 00AF234, 014F, etc. Is there a way to remove these leading zeroes (other than VB)? The data could be of any length and have 0, 1, 2 or more leading zeroes. Can't do find/replace because other zeroes (like in the middle of the data) would also be removed. Nothing I've tried seems to work, short of sorting by length, then doing =right. Any ideas please? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Probably a Stupid Question
Grandfeller,
Glad to hear it - more macros is a good thing ;-) Bernie MS Excel MVP Bernie, Not only does your macro make this task easy (I had the same problem this thread addresses), but convinced me to used VB macros more. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stupid question | New Users to Excel | |||
Stupid If statement question.... | Excel Discussion (Misc queries) | |||
Stupid Question | Excel Worksheet Functions | |||
Stupid List Box Question | Excel Discussion (Misc queries) | |||
Stupid counting question!! | Excel Discussion (Misc queries) |