Need help with brackets in excel

**MasseyHarry12** · 02-14-2013, 03:12 AM

Hey all,
Having trouble in excel. Ive got a 5,000 large set of data and i need to put the brackets < > around text. HOWEVER, although there are 5000 cells many of the cells in betwen are blank and i've been doing it in FORMAT CELLS then CUSTOM but this would take very long as i would have to go through and delete all the < > from the blank cells. Is there anyway in which i can do this without putting the brackets in blank cells

**benishiryo** · 02-14-2013, 03:30 AM

hi MasseyHarry12, welcome to the forum. so are you adding the brackets through Format Cells? if so & assuming your data starts from A2:A5000
1. select A2.*
2. click & drag the scroll bar to the end where the data of column A ends (row 5000)
3. press & hold the SHIFT key & left click on the last cell in column A (A5000). you should see them all selected
4. press CTRL + G -> Special -> Constants -> OK. now only non-blanks are selected
5. do not click anywhere else. press CTRL + 1 to format cells
6. Custom Format -> <@>
7. hit CTRL & ENTER

the above would work if column A are not formulas. if they are, try using formulas in B2:
=IF(A2="","","<"&A2&">")
copy it down to B5000. copy B2:B5000 & paste values in Column A. is that what you need? otherwise, uploading a sample Excel file in the thread will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

**MasseyHarry12** · 02-14-2013, 03:46 AM

yeh that second one worked thankyou so much! HOWEVER ive got one more problem, if some of the data already has < > when i did that it now has << >> is there anyway to make that not happen? cheers

**benishiryo** · 02-14-2013, 04:59 AM

if there's no possibility of just 1 bracket appearing, then try:
=IF(A2="","",IF(ISNUMBER(FIND("<",A2)),A2,"<"&A2&">"))

if there is, then maybe:
=SUBSTITUTE(SUBSTITUTE(IF(A2="","","<"&A2&">"),"<<","<"),">>",">")

**MasseyHarry12** · 02-14-2013, 07:41 PM

Thanks again! one more query, how would i add one of those equations to the equation already entered:

=IF(VLOOKUP($C9,'Account Registration'!$B$2:$Y$8596,18,FALSE)=0,"",VLOOKUP($C9,'Account Registration'!$B$2:$Y$8596,18,FALSE))

By using your equation it works but when i delete the original data to replace it with the new bracketed data it fails obviously because the old data has been deleted. So i need to find a way of adding one of your equations to my equation so it all gels,