Carriage Return - detect with formula

[velohead]

Hi All,

We use vlookups on staff names, but some staff think it funny to put the name "on two rows in the same cell". I believe this is called a carriage return ???
The problem is it looks ok, but is not ok.
Is there any way to detect them with a formula ?

 

[Cubist]

Try:

Book1
	A	B
1
2	a	FALSE
3	a b	TRUE
4	a	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=ISNUMBER(FIND(CHAR(10),A2))

 

[velohead]

Try:

Book1
	A	B
1
2	a	FALSE
3	a b	TRUE
4	a	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=ISNUMBER(FIND(CHAR(10),A2))

Yes, that works. Thank You.

Next question, out of curiosity, haw do I add the carriage return to Joe Bloggs to make...
Joe
Bloggs

 

[Cubist]

Next question, out of curiosity, haw do I add the carriage return to Joe Bloggs to make...
Joe
Bloggs

On Windows, Alt + Enter to insert carriage return inside in the formula.
On Macs, Option + Return.

 

[Peter_SSs]

Is there any way to detect them with a formula ?

Not sure if it might suit you but rather than just detect them, perhaps you could adapt your vlookup to cope with that sort of entry. Something like below.

Or you could do a Find/Replace to fix the entries and make them single-line by replacing the line break with a space?

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

25 02 05.xlsm
	A	B
1	Name	Department
2	Tom Jones	Sales
3	Ann Smith	Production
4	Fred Derf	Marketing
5
6
7	Enter Name	Department
8	Ann Smith	Production
9	Tom Jones	Sales
10	Fred Derf	Marketing
11	Tom Jones	Sales
CR

Cell Formulas
Range	Formula
B8:B11	B8	=VLOOKUP(TRIM(SUBSTITUTE(A8,CHAR(10)," ")),A$2:B$4,2,0)

 

[velohead]

Thanks Peter, for now I just need to detect but equally may wish to manipulate in the future, so I need to explore further.
Sorry for the delay replying, I have been away.

I have updated my profile as a Excel 365 user, but when I run in VBA..... X=Application.Version .....it returns 16, indicating excel 16.
I am confused.

Also, following upgrade to Windows 11 recently I have lots of niggling Outlook issues, can you recommend a forum at all ?

Thx.

 

[MARK858]

I am confused.

All Excel products after Excel 2016 are version number 16 as they stopped issuing version numbers after this (they use a build number now for precision as there are so many different versions that are current depending on what you or your company have signed up for).

The 365 option is the correct one for you to state on this forum (Ideally add the platform as well).

You can post Outlook questions in the forum below on this board, afraid that I can't recommend any of the specialist Outlook forums as I don't use them.

General Discussion & Other Applications

This forum is for discussion about any application & for questions about applications other than Excel or Access. This is also the place to ask about other spreadsheet programs (e.g. Google Sheets), book suggestions, or other websites.

www.mrexcel.com

 

[velohead]

I must admit that I had not run the X=Application.Version for a while until now, maybe it has been a good few years or even a decade. Where does the time go, lol.

Thanks for all the info, useful as always.