Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 2000?
Hello,
I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 2000?
Have you considered using a Pivot table to accomplish what you want?
-- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 2000?
If you import the data into Excel directly, is there a single blank row
between each of the records? Something like: Joe Smith 128 Maple Ave Springfield IL David Jones 12 Main Street Chicago IL -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 2000?
Try to find ^p and replace it with *^p
How will you indicate the End of Record? "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 200
Hi Gary''s Student -
Yes, there is a single blank line - just like you describe when I import it into Excel. Is there an easy way I can get the data into my desired format because of that blank line? Thank you!! Mo "Gary''s Student" wrote: If you import the data into Excel directly, is there a single blank row between each of the records? Something like: Joe Smith 128 Maple Ave Springfield IL David Jones 12 Main Street Chicago IL -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 200
If we put the original data in Sheet1 and leave Sheet2 blank, then this
little macro: Sub reformatit() Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim m As Long, n As Long, i As Long, j As Long s1.Activate n = Cells(Rows.Count, "A").End(xlUp).Row i = 1 j = 1 For m = 1 To n v = Cells(m, 1).Value If v = "" Then j = 1 i = i + 1 Else s2.Cells(i, j).Value = v j = j + 1 End If Next End Sub will create: joe smith 123 maple ave sprintfield IL mary williams apartment 54 12 main street atlanta ga james ravenswood 12 carter ave princeton nj starting from raw data that looks like: joe smith 123 maple ave sprintfield IL mary williams apartment 54 12 main street atlanta ga james ravenswood 12 carter ave princeton nj The macro looks for a blank record to start the next output row. Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm REMEMBER: This macro uses Sheet1 and Sheet2 specifically. -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hi Gary''s Student - Yes, there is a single blank line - just like you describe when I import it into Excel. Is there an easy way I can get the data into my desired format because of that blank line? Thank you!! Mo "Gary''s Student" wrote: If you import the data into Excel directly, is there a single blank row between each of the records? Something like: Joe Smith 128 Maple Ave Springfield IL David Jones 12 Main Street Chicago IL -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I add a character to the end of every row in Excel 200
Hi Again GS -
YAY - it worked like a champ! I only had to do some other minor editing to get the columns to line up correctly which I did manually. THANK YOU SOO MUCH!!! Mo =) p.s. I have one two more edits that I'd like to do on the sheet. This is my first time here and I don't know what the proper etiquette is - may I ask you directly here in this thread or should I open a new post? "Gary''s Student" wrote: If we put the original data in Sheet1 and leave Sheet2 blank, then this little macro: Sub reformatit() Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim m As Long, n As Long, i As Long, j As Long s1.Activate n = Cells(Rows.Count, "A").End(xlUp).Row i = 1 j = 1 For m = 1 To n v = Cells(m, 1).Value If v = "" Then j = 1 i = i + 1 Else s2.Cells(i, j).Value = v j = j + 1 End If Next End Sub will create: joe smith 123 maple ave sprintfield IL mary williams apartment 54 12 main street atlanta ga james ravenswood 12 carter ave princeton nj starting from raw data that looks like: joe smith 123 maple ave sprintfield IL mary williams apartment 54 12 main street atlanta ga james ravenswood 12 carter ave princeton nj The macro looks for a blank record to start the next output row. Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F8 2. Select the macro 3. Touch RUN To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm REMEMBER: This macro uses Sheet1 and Sheet2 specifically. -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hi Gary''s Student - Yes, there is a single blank line - just like you describe when I import it into Excel. Is there an easy way I can get the data into my desired format because of that blank line? Thank you!! Mo "Gary''s Student" wrote: If you import the data into Excel directly, is there a single blank row between each of the records? Something like: Joe Smith 128 Maple Ave Springfield IL David Jones 12 Main Street Chicago IL -- Gary''s Student - gsnu200823 "Mostifer" wrote: Hello, I "inherited" a Word document that has more than 19000 rows when I export it into Excel. It's an address list that is nearly useless to me in a Word format. What I really would like to do is to automatically reformat it so that I can use it in Excel... however, I'm afraid that it's not possible to do that. Essentially, I have addresses where sometimes they display with the following info: Name Business Street Address City, State ZIP Other times an entry is: Name Title Business Street Address 1 Street Address 2 City, State ZIP Each of the line items above are in their own row when I copy it into Excel. What I would LOVE is to have each entry in its own individual row with multiple columns like: Name Business Address City State ZIP Or something similar. However, since I wasn't able to figure out how to do that, I thought I would add a character like an asterisk to the end of each row, copy it into a Notepad file, then import it into Excel and have it recognize the asterisks as the delimiter. BUT, I couldn't figure out how to add an asterisk to the end of every row. Does anyone know how to do that? Or to do the formatting natively within Excel so I don't have to add the character then re-import it? Thank you!!! Mo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000: How to find a certain character in ANY Column? | Excel Discussion (Misc queries) | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
I cannot edit cell format in Excel 2000 (Part of office 2000)! | Excel Discussion (Misc queries) | |||
Using Excel 2000 as Data source for Word 2000 document | Excel Discussion (Misc queries) | |||
Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) |