Solved Copying all text before an underscore _ into another cell

September 28, 2018 at 08:46:20
Specs: Windows 7
I have various codes which I need to copy into another cell. The file names are always separated from the code by an underscore. As an example:

1.0_0123456_20180907_Test Report
1.7.2.2.12_0123456_20180907_Test Report

I only need to copy the 1.0 or the 1.7.2.2.12 into another cell.


See More: Copying all text before an underscore _ into another cell

Reply ↓  Report •

#1
September 28, 2018 at 09:36:40
✔ Best Answer
Try this:

=LEFT(A1,FIND("_",A1)-1)

The LEFT Function has these arguments:

=LEFT (text, [num_chars])

"text" is the cell reference from which you want to extract the characters on the left.
"num_chars" in the number of characters that you want to extract.

The FIND function has these arguments:

=FIND(find_text, within_text, [start_num])

"find_text" is the character that you want to find.
"within_text" is the cell reference in which to find the character.
"start_num" is an optional argument that allows the user to start looking for the character at a location other than the beginning of the string.

Since you want to extract the characters before the first underscore, we let the FIND function find the underscore (position 4 and position 11 in your examples) and then subtract 1 from that result. The LEFT function will then return that number of characters from the referenced cell.

Let us know of you have any other questions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question