This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / Ask help from VBA expertiseHi there, I have one Excel file. I need to change one of the existing column's format. The data in the column looks like "AAAAA00000BB". I need to change it to "AAAAA-00000-BB". There are thousands of such rows in the file. I don't want to change each of them manually. It's a pain. I think VBA probably can help me to do it automatically. But I don't know VBA at all. Can someone help me please? Is it possible that VBA can resolve this problem? Thanks in advance.
-raindot(三年不苦);
2005-11-9
{473}
(#2598485@0)
-
You can do it in Excel with functions without any fancy VBA.First chop up your original string into 3 columns with will show AAAA, 00000, and BB. This can be done, separately, using the "Left Mid, Right" functions. Just search for each function in the drop-down functions menu. It's self explanatory.
The next step is join the three new columns (Say they are column X,Y,Z, on the 2 row) into a new column AA. function is AA=X2&"-"&Y2&"-"&Z2. Voila.
-hilo(still mellow);
2005-11-9
{403}
(#2598526@0)
-
Thank you very much. It works.
-raindot(三年不苦);
2005-11-9
(#2598636@0)
-
=LEFT(A1,5) & "-" & MID(A1,6,5) & "-" & RIGHT(A1,2)
-schen(睹往睹来);
2005-11-9
(#2599046@0)
-
thank you too.
-raindot(三年不苦);
2005-11-9
(#2599057@0)
-
Sorry, one more question. If I need to delete the duplicate rows in an Excel, how can I do it except manually?
-raindot(三年不苦);
2005-11-9
(#2599059@0)
-
Add another column to mark the duplicated row...in that column you can write something like:
=If (AND(A1=A2,B1=B2.....), "duplication","")
fill down the colum in that list. The rows with "duplication" marks then can be deleted.
Hope that helps.
-schen(睹往睹来);
2005-11-9
{211}
(#2599104@0)
-
It does help. However......My file is thousands of rows. I can sort data first. Then I need to find out the duplicate rows, sometimes 2 rows, sometimes 5 rows, it depends. Then keep one row, delete all other duplicate rows. Am I clear? Anyway, thank you in advance.
-raindot(三年不苦);
2005-11-9
{238}
(#2599133@0)
-
On Excel menu bar, click "Data" then "Filter". You can filter for unique records. Check out Excel's help for steps.
-hilo(still mellow);
2005-11-9
(#2599123@0)
-
No VBA is necessary. Click on the top left corner of your spreadsheet to highlight the entire sheet, on menu bar, click format->cells->custom, type in your desired format.
-23456789(大白呼);
2005-11-10
(#2601208@0)
-
Sorry, I tried this way first. It didn't work. :-(((. Anyway, thank you.
-raindot(三年不苦);
2005-11-10
(#2601615@0)
-
Still, it has nothing to do with VBA, Excel allows cutomized number formating only. For text, there is a list of common format styles for postal code, phone number, and SIN number, nothing else is allowed. :(
-23456789(大白呼);
2005-11-11
(#2602446@0)
-
Yes. You are rightif you use this way to set the format for the new data. However, my issue is I need to change the format of the existing data in the cell. I tried to set custom format as "#####-##-##". When I entered new data, it did work. For the existing data, it ................ :-(((
-raindot(三年不苦);
2005-11-11
{274}
(#2603448@0)
-
Send me an example Sheet and I'll do it for you in two ways: [1] Excel Function [2]VBA.
It'll only take me 10 mins. And typing it here will take me more than that :)
-schen(睹往睹来.非赌徒也!);
2005-11-12
(#2604122@0)
-
Thank you. I figured out the issue with your previous method. No VBA, only Excel function.
-raindot(三年不苦);
2005-11-12
(#2604630@0)