# Boards

## EXCEL NERDS: assemble

This is probably piss easy but I barely know how to use the fucking thing: is there a way to reformat cells in which the content is set out as "a/b/c" to "ab-c"?

Can't really be arsed doing 400 of the fuckers by hand.

This is probably piss easy but I barely know how to use the fucking thing: is there a way to reformat cells in which the content is set out as "a/b/c" to "ab-c"?

Can't really be arsed doing 400 of the fuckers by hand.

## those aren't mathematical operators, btw

Just text.

## I would just export to CSV

and then open in Notepad++ and use the REGEX Ctrl+H functionality to do it, before reopening the CSV in Excel.

## that's a good idea

Unfortunately I'm on a typically locked down corporate system where I can't install a less useless text editor.

## is "a/b/c" a text string or a date?

## text string

## If the "a/b/c" is in cell A1 then:

=LEFT(A1,FIND("/",A1)-1)&MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"-"&RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1))

## FFS that's way neater than my effort

## having to use excel quite a lot recently for something

would rather not have to use it

## Text to columns that mother fucker with / as your delimiter

And then stitch it back together again with & and shit.

## oh yeah, or that

my formula is cooler though

## Yeah but takes so much longer

well not anymore as he can just copy it.

## This works if your data is in A1

=LEFT(A1,FIND("/",A1)-1)&MID(A1,LEN(LEFT(A1,FIND("/",A1)-1))+2,FIND("/",RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("/",A1)-1))-1))-1)&"-"&RIGHT(RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("/",A1)-1))-1),LEN(RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("/",A1)-1))-1))-FIND("/",RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("/",A1)-1))-1)))

=substitute(substitute(a2,''/'',''-''),''-'','''',1)

## Smart

http://cuddlebuggery.com/wp-content/uploads/2015/03/its-so-beautiful-gif.gif

## just use text to columns function, delimited by "/"

and then concatinate into desired format

## oh i'm just balonz but slower

## Christ mate, you're not *that* bad.

## since I can't copy and paste from my phone

Text to columns then concatenate worked beautifully.

Thanks all, and congratulations to Balcxxonkz for winning the thread. There is no prize.

-----THREAD CLOSED-----

I'm not normally boastful but my way was definitely the best

## Yeah, yours looked fucking mint, tbh.