0

datasnapshot

I have a huge dataset where one of the columns is in a format as given above. The thing is that I want to extract(cut) the name of the city(eg. VATICAN as given below) from the column and copy it to another column to the right side of it which is empty.

However the problem here is that for many records there is no information on the city. So is it possible to cut from the column(IF there is a '-' or ':' in the first few characters in the column and cut it from that column and paste in the same row but adjacent column)? Or is there another way round to do such a thing?

Desired Output: enter image description here

5
  • You received an answer that satisfied your own needs, but there are too many variables for this to be generally helpful or reliably answerable. How do you know what line to look at for the city? Are you only interested in a city name that appears at the beginning of a certain line? What if the city name contains a hyphen? How consistently is the city delimited from the text (hyphens appear everywhere; is Reuters always going to follow the city or might other news services be there?). Etc.
    – fixer1234
    Feb 13, 2019 at 5:53
  • What do you recommend? Is there a tool(other than Excel) that I should use?
    – Zaid Khan
    Feb 13, 2019 at 10:46
  • You got a solution that works for you. My comment was about the ambiguity in the question and how it affects its usefulness as-is (which is why it got closed). If you could edit it to be more more specific about the range of data possibilities and the rules you apply for identifying the city, we might be able to reopen the question.
    – fixer1234
    Feb 13, 2019 at 10:53
  • okay. I've done the edits. Have a look.
    – Zaid Khan
    Feb 13, 2019 at 16:49
  • The edit doesn't really help, and it invalidates the existing answer. This looks like a fuzzy logic problem that humans are good at, but it's a bear to define precisely enough for a simple Excel formula. I'll roll it back so that the answer makes sense at least.
    – fixer1234
    Feb 13, 2019 at 18:47

1 Answer 1

1

You could use the combination of =LEFT() and =SEARCH().

SEARCH gives you the characters position in a string and LEFT returns every character on the left of the position you want.

For example, for the Cell content in A1 = VATICAN - The Pope...
=SEARCH("-",A1) will give you 6
=LEFT(A1,6) will give you VATICAN -
and the combination
=LEFT(A1,SEARCH("-",A1)) will return VATICAN -

The same works for any other character as i.e. ; or :

Note that the LEFT() function will get you also the searched character. You can append a -1 after the SEARCH() function to avoid it.


If the character is not found, you will get an error, this is how you overcome it:
=IFERROR(RETURN,RETURN_IF_ERROR)

If you want to write for example (unknown) you should use
=IFERROR(LEFT(A1,SEARCH("-",A1)-1),"unknown") but you can also leave it blank by puttnig only "".


Concatenation:

You can concatenate this formulas several times to get different characters. Keep in mind that Excel will work the formula ltr.

If you want to search for "-" and, if not found, for ":" you would use:

=IFERROR(LEFT(A1,SEARCH("-",A1)-1),IFERROR(LEFT(A1,SEARCH(":",A1)-1),"unknown"))

EDIT:

If you want to look only in the first, let's say, 20 characters, just change SEARCH("-",A1) for SEARCH("-",LEFT(A1,20))

2

Not the answer you're looking for? Browse other questions tagged .