 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / November 2007Range Breakup
I am looking for a way to break up a range into separate cells. For example, I have a range of zip codes in one cell that looks like this: 99359-99363 I would like that to break into 5 cells, with values of 99359, 99360, 99361, 99362, and 99363. hi if the long zip is in A1 then in B1 put... =left(A1,5) then in C1 put =B1+1 copy and paste in D1 and E1 worked for me Regards FSt1 > I am looking for a way to break up a range into separate cells. For example, > I have a range of zip codes in one cell that looks like this: 99359-99363 I > would like that to break into 5 cells, with values of 99359, 99360, 99361, > 99362, and 99363. That does work. However, there are 2000 of these instances, and they do not all have the same length of range. Any suggestions for a case like this? > hi > if the long zip is in A1 then in B1 put... [quoted text clipped - 11 lines] > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > 99362, and 99363. hi i assumed that you example was like all the others. post other examples. you may have to do a different left formula for each unique zip. lets see. regards FSt1 > That does work. However, there are 2000 of these instances, and they do not > all have the same length of range. Any suggestions for a case like this? [quoted text clipped - 14 lines] > > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > > 99362, and 99363. More examples: 99332 - 99333 99343 - 99350 99356 - 99357 99359 - 99363 99401 - 99403 The problem is that some only need to be broken out twice, others five times; I could do the "left" method that you talked about, but I would have to go through all 2000 records individually. > hi > i assumed that you example was like all the others. post other examples. you [quoted text clipped - 21 lines] > > > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > > > 99362, and 99363. Okay, carrying on from FSt1's earlier suggestion, put the LEFT function in B1, then in C1 put this: =IF(B1="","",IF(1*RIGHT($A1,5)>=B1+1,B1+1,"")) The formula can be copied across the row for the largest range you expect (i.e. into I1 in your example), then these formulae from B1 to I1 can be copied down for as many entries as you have in column A. Hope this helps. Pete > More examples: > 99332 - 99333 [quoted text clipped - 34 lines] > > - Show quoted text - Try this: First, put your start/end values in A1:B5 99332 99333 99343 99350 99356 99357 99359 99363 99401 99403 Then.... D1: =MIN($A$1:$A$5) This formula is broken into sections for readability D2: =IF(MAX($D$1:$D1)=MAX($B$1:$B$5),"", IF(ISNA(MATCH(D1,$B$1:$B$5,0)),D1+1, INDEX($A$1:$A$5,MATCH(D1,$A$1:$A$5,1)+1))) Copy D2 down as far as you need. Note: if there are to be leading zeros, you'll need to reformat those cells to show them. Using your example, those formulas return: 99332 99333 99343 99344 99345 99346 99347 99348 99349 99350 99356 99357 99359 99360 99361 99362 99363 99401 99402 99403 Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I am looking for a way to break up a range into separate cells. For >example, > I have a range of zip codes in one cell that looks like this: 99359-99363 > I > would like that to break into 5 cells, with values of 99359, 99360, 99361, > 99362, and 99363. That's exactly what I needed. Thanks a lot. > Try this: > [quoted text clipped - 56 lines] > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > 99362, and 99363. You're very welcome.....I'm glad I could help. *********** Regards, Ron XL2003, WinXP > That's exactly what I needed. Thanks a lot. > [quoted text clipped - 58 lines] > > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > > 99362, and 99363. hi sorry it took me so long to get back. something came up i had to take care of. but i see you have been taken care of by others. i haven't had a chance to work on your examples but it would seem you have what you need. sorry FSt1 > That's exactly what I needed. Thanks a lot. > [quoted text clipped - 58 lines] > > > would like that to break into 5 cells, with values of 99359, 99360, 99361, > > > 99362, and 99363. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.