Carriage Return - detect with formula

velohead

Board Regular
Joined
Aug 22, 2007
Messages
222
Office Version
  1. 365
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 ?
 
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.
 
Upvote 0
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
AB
1NameDepartment
2Tom JonesSales
3Ann SmithProduction
4Fred DerfMarketing
5
6
7Enter NameDepartment
8Ann SmithProduction
9Tom JonesSales
10Fred DerfMarketing
11Tom JonesSales
CR
Cell Formulas
RangeFormula
B8:B11B8=VLOOKUP(TRIM(SUBSTITUTE(A8,CHAR(10)," ")),A$2:B$4,2,0)
 
Upvote 0
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.
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top