|
Nov 2022
7:08pm, 17 Nov 2022
19,700 posts
|
3M (aka MarkyMarkMark)
I saw that on FB earlier. I did think it'd be better in Word... 
|
|
Nov 2022
6:28pm, 19 Nov 2022
5,899 posts
|
quimby
Bought myself some socks.
|
|
Nov 2022
1:24pm, 23 Nov 2022
74,499 posts
|
Diogenes
I had a little problem to solve today which was slightly trickier than it looked at first sight. I dare so most of you lot would have got this much more quickly, and in more sophisticated ways, and I did start typing this as a question, but it was more fun working it out for myself
I have some 700 hundred rows of data that was exported into Excel from another application. In one column there is a long string of text which I wanted to truncate to get rid of everything after the first space.
I removed everything after the first space using =LEFT(A1,FIND(" ",A1)-1)
This worked, except what looks like a space after the text I want is actually some kind of carriage return or line break. Google told me that the easiest way to get rid of this was to use Find and Replace, searching for CRTL+J and replacing with “ “
This didn’t work as Excel didn’t find anything to replace. Then I used the CLEAN function [CLEAN(A1)] which removes all non-printable characters from cells. This worked but left me with new problem: I now had an unwanted word directly after the text I wanted to keep. As there was no consistent character or position to use as a reference point from which to delete, I had to find another solution.
This turned out to be to use =SUBSTITUTE(A1,CHAR(10)," ")
(I don’t know why substituting CHAR(10) works when CRTL-J doesn’t in Find & Replace when, as far as I understand it, they are the same thing.)
So, the short version is to:
1) =SUBSTITUTE(A1,CHAR(10)," ") [save the results as values]
2) =LEFT(A1,FIND(" ",A1)-1) [save as values]
Now I’ve got the data clean I just need to work with it.
There may well be further challenges ahead.
|
|
Nov 2022
2:28pm, 23 Nov 2022
5,913 posts
|
quimby
You know what I would have done? Copied that column into Word and done a find and replace there. You can replace things like "^t", "^l" or "^p" (tab, line break, paragraph break) with "". It's a lot more reliable than the find/replace in Excel, IMO.
|
|
Nov 2022
10:58pm, 23 Nov 2022
6,946 posts
|
Pothunter
My first though was Text to Columns, although I haven’t tried it with special characters…
|
|
Nov 2022
6:46am, 24 Nov 2022
19,744 posts
|
3M (aka MarkyMarkMark)
Funny, whenever I have to do something like that I nearly alway end up back in Word!
Usually followed by Mk.1 eyeball..... 🧐
|
|
Nov 2022
7:53am, 24 Nov 2022
16,982 posts
|
Garfield
Word, then text to columns. I've done the opposite at times...so tabular material I've pasted into Notepad++ to get rid of the cells, then doing various manipulations to get the data into shape for whatever purpose I wanted it. Frequently done when I did the 1500 miles in [year] for my roundup.
|
|
Nov 2022
8:08am, 24 Nov 2022
74,516 posts
|
Diogenes
I didn't think of that. I'm going to give it a try to see how well it copes as a comparison.
|
|
Nov 2022
9:59am, 24 Nov 2022
74,519 posts
|
Diogenes
This is one that I think I need the hive mind's help with.
I want to enter a value in a cell based on matching the value of one with the same text appearing anywhere in a text string in another. Here's an illustration:
A2 = X1B2C3NM12
B1 = C3
I want to put a formula in B2 that looks for the value of B1 anywhere in A2 an enters a value, "1" or "Y" if a match is found.
|
|
Nov 2022
10:10am, 24 Nov 2022
6,748 posts
|
um
I'd use =IF(IFERROR(FIND(B1,A2,1),""),1)
|
