Find and replace not recognising contents of cells

EmmaN

New Member
Joined
Jun 17, 2014
Messages
21
Hi,

I am trying to find and replace data in a cell but its not being recognised.

The data has been exported from our MIS system, it exports with a carriage return in it. I need to remove the carriage return and then the staff initials at the end of the text string.

I've tried removing the staff initials with the carriage return (G9) and not removing the carriage return (F9), but neither methods allow the data to be recognised in find and replace. The data I want to remove is ' RW' with a space before it because of the carriage return or just 'RW'.

I've used =F9=" RW" function in cell C3 to check the data in cell F9 or G9, but get FALSE returned.
I've added =LEN in cell D3 to check the number of characters in cell F9 or G9, to see if there are any hidden characters. The number is correct.
I've tried copying and pasting the contents of cell F9 or G9 into the find and replace function. Still not recognised.
I've added RW to cell G12 on its own. Still not recognised.

The sheet isn't protected. I've tried saving it as 2007. Totally stumped as to why I cant find and replace. Any ideas gratefully received.

Thank you.
 

Attachments

  • Find and replace.PNG
    Find and replace.PNG
    24.8 KB · Views: 8

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
238
Office Version
  1. 365
Platform
  1. Windows
Hi Dear,

Please try to sue =TRIM function

Otherwise, please share the data set using XL2BB so we can provide adequate support.

Regards
 

EmmaN

New Member
Joined
Jun 17, 2014
Messages
21
master_spreadsheet (24).xls
ABCDEFGHIJKLM
2LockersP1P2P3P4P5P6P7P8P9P10
3AR1AR1FALSE13BTECDBS/BgBTECDBS/Bg12DHS/Vhx SI
4Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A88#N/A#N/A
5Room Capacaity 1.5m20#N/A#N/A#N/A#N/A#N/A#N/A#N/A1212#N/A#N/A
6AR2AR2
7Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
8Room Capacaity 1.5m20#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
9AR3 - BTECAR3 - BTECBTECDBA/Ba RWBTECDBA/Ba RWBTECDBA/BaBTECDBA/Ba RWBTECDBA/Ba NDBTECDBA/Ba ND
10Set count#N/A#N/A#N/A#N/A#N/A4#N/A#N/A#N/A#N/A#N/A
11Room Capacaity 1.5m2000000000000
12AR4AR4RW9DLP/Ar ND
13Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
14Room Capacaity 1.5m2000000000000
15AUDAUD9DRW/Dr ECO
16Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
17Room Capacaity 1.5m2000000000000
18C1C110DJE/Mt JEL7DA1/Af TA7DA1/Af TA7DA2/Af TA7DA2/Af TA10DA3/En KAC10DA3/En KAC
19Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
20Room Capacaity 1.5m2000000000000
21C10C1010DEC/Mt EC13DA1/F3 EC13DA1/F3 EC10DAF2/Fr EC10DAF2/Fr EC10DB2/En JNA10DB2/En JNA13DHL1/E1 AM
22Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
23Room Capacaity 1.5m2000000000000
24C11C1111DDM/Mt DMC13DS1/S2 CA13DS1/S2 CA10DAS1/Sp CA10DAS1/Sp CA10DB3/En MAM10DB3/En MAM11D3/Ma GMC
25Set count#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
JESS 2020-2021
Cell Formulas
RangeFormula
C3C3=F9=" RW"
D3D3=LEN(F9)
C4:M4,C25:M25,C22:M22,C19:M19,C16:M16,C13:M13,C10:M10,C7:M7C4=VLOOKUP(C3,'[master_spreadsheet room capacity 10.11.2020.xlsx]Set Count 09.11.2020'!$A$1:$C$918,2,FALSE)
C5:M5C5=SUM($B$5-C4)
C8:M8C8=SUM($B$8-C7)
C11:M11,C23:M23,C20:M20,C17:M17,C14:M14C11=SUM(C11-C10)


Mini sheet uploaded. I need to remove the initials after the space at the end of the string in the while rows.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What does the formula =CODE(G12) return?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can you also try =CODE(RIGHT(G12, 1))
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,218
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The 82 is the ASCII code for R and 87 is the ASCII code for W. Neither is showing a carriage return or any other non-printing character.
 

EmmaN

New Member
Joined
Jun 17, 2014
Messages
21
The 82 is the ASCII code for R and 87 is the ASCII code for W. Neither is showing a carriage return or any other non-printing character.
Thank you for your help. I'm absolutely stumped as to why I cant find and replace in this sheet. It behaves differently everytime I export it from my MIS. Sometimes i can find and replace, other times i cant but i can't see why.
 

Forum statistics

Threads
1,144,236
Messages
5,723,169
Members
422,479
Latest member
Mr_Confused

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
Top