for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

um Dec 2023 10:41am, 13 Dec 2023 8,015 posts	Quote Pin um I've only just realised I missed this fmworldcup.com Or The Times report thetimes.co.uk
Merry Christmas and Happy NewG(rrr) Dec 2023 1:11pm, 15 Dec 2023 48,910 posts	Quote Pin Merry Christmas and Happy NewG(rrr) I'm sure someone can do a better job than me on a Pareto log of the Confessions fetcheveryone.com/forum/anonymous-confessions-62525/24 Couldn't work out how to do X axis labels that represented Nice, Very Nice, Not Nice etc. G
stuart little Dec 2023 5:04pm, 15 Dec 2023 2,488 posts	Quote Pin stuart little Happy, not done in Excel, but clearly everyone is more forgiving than not overall!
Pothunter Jan 2024 3:55pm, 12 Jan 2024 8,576 posts	Quote Pin Pothunter
GregP Jan 2024 4:44pm, 12 Jan 2024 150,191 posts	Quote Pin GregP Brilliant
JamieKai chameleon Jan 2024 12:22pm, 16 Jan 2024 18,223 posts	Quote Pin JamieKai chameleon Can anyone assist me? I have a column of data being produced by Admin of names. Some names are normal, ie "Bob Smith", others are reverse, ie "Smith, Bob". I want to resolve these so they are uniform - is there a way of running an IF function to find if there is a comma in a text string? I've had a hunt but nothing is jumping out!
3M Jan 2024 12:28pm, 16 Jan 2024 23,078 posts	Quote Pin 3M Does it help if you do it in a few steps within the statement.... Search for the presence of a comma, measure the length of the overall string, then left$ and right$ either side of the comma, and drop it to a new cell? There may be a more elegant way - I'd need a copy of Excel open in front of me tp play to remember the syntax etc.
CBN Jan 2024 12:41pm, 16 Jan 2024 113 posts	Quote Pin CBN I think you need the newer version of excel for this to work: =IFERROR(TEXTAFTER(A1,", ")&" "&TEXTBEFORE(A1,", "),A1) Just change A1 for the column where your names are.
JamieKai chameleon Jan 2024 12:44pm, 16 Jan 2024 18,224 posts	Quote Pin JamieKai chameleon I think a stepped approach will certainly be tidier for what I want - mu ultimate aim is to strip out middle names to just leave forenames and surnames. I've currently got this Frankenstein formula working - but hell is it messy: =CONCAT(TEXTBEFORE(IF(IFERROR(SEARCH(",",A2),0)>0,CONCAT(TEXTAFTER(A2,", ")," ",TEXTBEFORE(A2,",")),A2)," ")," ",TEXTAFTER(IF(IFERROR(SEARCH(",",A2),0)>0,CONCAT(TEXTAFTER(A2,", ")," ",TEXTBEFORE(A2,",")),A2)," ",-1))
JamieKai chameleon Jan 2024 12:46pm, 16 Jan 2024 18,225 posts	Quote Pin JamieKai chameleon I think you need the newer version of excel for this to work: =IFERROR(TEXTAFTER(A1,", ")&" "&TEXTBEFORE(A1,", "),A1) Just change A1 for the column where your names are. Ooh - what do the ampersands do in that formula?