Excel: Left, Right, & Len text functions to separate data by Chris Menard
7.2 هزار بار بازدید -
6 سال پیش
-
In Excel, I have a
In Excel, I have a column with City, State, and Zip codes combined. A comma does not separate the data so text to columns will not work.
To analyze the data by city or state or zip, I need to break them out into three separate columns. I'm going to use the Text Functions Left, Right, and Len to handle this.
The reason Text to Columns will not work is the data is displayed without a comma.
Atlanta GA 30304
New York NY 11111
San Diego CA 99922
Left Function
**************
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
Example: if A1 contains GA-201. In B1 type =left(A1,2) and press Enter. It will return GA in B1. It pulled two characters from the left.
Right Function
**************
A1 still contains GA-201. In C1 type =Right(A1,3) and press Enter. Cell C1 will show 201. It pulled three characters from the right.
http://chrismenardtraining.com
A free course of Ten Reasons to use Tables in Excel
https://chrismenardtraining.teachable...
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera – https://amzn.to/3vdgF5E
○ Microphone - https://amzn.to/3gphDXh
○ Camera tripod – https://amzn.to/3veN6Rg
○ Studio lights - https://amzn.to/3vaxyy5
○ Dual monitor mount stand - https://amzn.to/3vbZSjJ
○ Web camera – https://amzn.to/2Tg75Sn
○ Shock mount - https://amzn.to/3g96FGj
○ Boom Arm - https://amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording – Camtasia – https://chrismenardtraining.com/camtasia
○ Screenshots – Snagit – https://chrismenardtraining.com/snagit
○ YouTube keyword search – TubeBuddy – https://www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
To analyze the data by city or state or zip, I need to break them out into three separate columns. I'm going to use the Text Functions Left, Right, and Len to handle this.
The reason Text to Columns will not work is the data is displayed without a comma.
Atlanta GA 30304
New York NY 11111
San Diego CA 99922
Left Function
**************
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
Example: if A1 contains GA-201. In B1 type =left(A1,2) and press Enter. It will return GA in B1. It pulled two characters from the left.
Right Function
**************
A1 still contains GA-201. In C1 type =Right(A1,3) and press Enter. Cell C1 will show 201. It pulled three characters from the right.
http://chrismenardtraining.com
A free course of Ten Reasons to use Tables in Excel
https://chrismenardtraining.teachable...
And make sure you subscribe to my channel!
○ My camera – https://amzn.to/3vdgF5E
○ Microphone - https://amzn.to/3gphDXh
○ Camera tripod – https://amzn.to/3veN6Rg
○ Studio lights - https://amzn.to/3vaxyy5
○ Dual monitor mount stand - https://amzn.to/3vbZSjJ
○ Web camera – https://amzn.to/2Tg75Sn
○ Shock mount - https://amzn.to/3g96FGj
○ Boom Arm - https://amzn.to/3g8cNi6
○ Screen recording – Camtasia – https://chrismenardtraining.com/camtasia
○ Screenshots – Snagit – https://chrismenardtraining.com/snagit
○ YouTube keyword search – TubeBuddy – https://www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
6 سال پیش
در تاریخ 1397/06/14 منتشر شده
است.
7,250
بـار بازدید شده